Remove parts of string

[Guest]

Hi all.

I need some advice with this. From a list object in a form, users can choose multiple options. Particularly, it's students being interested in courses. The form has so far been sent as an email. Now the marketing dept wants to use an emailing db - so I need to work with the existing form and put the values into the DB, additional to the email. In other words, I'm not supposed to change anything on the existing forms. When they choose the courses, the data passed on looks like this;

126211 Graduate Certificate in Banking,128511 Graduate Certificate in Entreprene

In the DB, they only want the course number. How can I get rid of everything, but the course numbers (divided by commas). I reckon, it involves something with using the comma as a delimiter...but can't quite figure it out.

Thanks for your help.

Adrian Wagner

[Guest]

I figured it out myself.l Here's the code. I'd be open to smarter ways for this:

<cfset newString = "">
<cfloop list="#originalString#" index="i">
<cfset newString = newString & #val(i)# & ",">
</cfloop>
<cfset stringLen = len(newString)>
<cfset stringlen = stringlen -1>
<cfset newstring = left(newstring, stringlen)>

[drforbin1970]

What type of DB are you using? SQL Server?

[drforbin1970 · Accepted solution]

Since you know the input from the form(which you stated cannot be changed), the following examples should work. Example 1 uses a regular expression to replace characters. Example 2 converts beginning of a string to a number.

The regular expression in Example 1 replaces a-z and a blank space. You can add to the regular expression if other characters need to be replaced.

<cfset mylist = "126211 Graduate Certificate in Banking,128511 Graduate Certificate in Entreprene">
<!--- EXAMPLE 1 --->
<cfset y = REReplaceNoCase(mylist,"[a-z ]","","ALL")>
<!--- LIST OF COMMA-DELIMITED NUMBERS --->
<cfoutput>#y#</cfoutput>
<br>
<!--- LOOP THRU LIST AND GET EACH NUMBER --->
<cfoutput>
<cfloop list="#y#" index="i">
<cfif IsNumeric(i)>
#i#<br>
</cfif>
</cfloop>
</cfoutput>

<!--- EXAMPLE 2 --->
<cfparam name="z" default="">
<cfloop list="#mylist#" index="i">
<cfif i NEQ ListLast(mylist)>
<!--- IF NOT LAST ITEM IN LIST, ADD COMMA --->
<cfset z = z & Val(i) & ",">
<cfelse>
<cfset z = z & Val(i)>
</cfif>
</cfloop>
<br>
<!--- LIST OF COMMA-DELIMITED NUMBERS --->
<cfoutput> #z#</cfoutput>