trifecta2k
asked on
Find a string within a string
I have a 2 columns in a table that have the following type of information:
Col 1 Col 2
A4 Adminstrator A4
A5 Analyst A5
Basically this data was pushed to our database from a different source. When I display the information with Cold Fusion they want the display to look like this:
A4 - Adminstrator
So they want the A4 taken off the end of the col2, but they don't want it deleted from the database. What's do you think is the best way? I think if I find col1 in col2 and then trim the col 2 based on location. By the way not all of the codes are 2 letters. Some are 3, some are 4, some don't have any.
Col 1 Col 2
A4 Adminstrator A4
A5 Analyst A5
Basically this data was pushed to our database from a different source. When I display the information with Cold Fusion they want the display to look like this:
A4 - Adminstrator
So they want the A4 taken off the end of the col2, but they don't want it deleted from the database. What's do you think is the best way? I think if I find col1 in col2 and then trim the col 2 based on location. By the way not all of the codes are 2 letters. Some are 3, some are 4, some don't have any.
ASKER
just to be sure, should it be col1, then col2? you have them both listed as col2. Thanks
Using your example might use something like this...
<cfset positiontostop = (findoneof('A4', col2) -1)>
<cfset displaythis = mid(col2, 1, positiontostop)>
<cfset positiontostop = (findoneof('A4', col2) -1)>
<cfset displaythis = mid(col2, 1, positiontostop)>
I don't think replace is a good option. The letters of col1 could be found within the text of col2 and would therefore be stripped out.
If you are sure that col1's letters are the last characters of col1, then simply take all but those last X number of characters using the length function..
trim( mid(col2, 1, len(col2)-len(col1)) )
I don't believe findoneof() would work.
That function finds only one character of the list. So if the value were A4 it would index on either A or 4, thus truncating at the first occurance of either A or 4.
That function finds only one character of the list. So if the value were A4 it would index on either A or 4, thus truncating at the first occurance of either A or 4.
ASKER
gdemaria
Your method would work, but some of the col2 don't have col1 at the end. I guess that I didn't make that clear. Sorry. Basically we have 2 old sets of data pushed into this new table. So some are:
Col1 Col2
a4 Admin a4
and some are
Col1 Col2
a4 admin
I only need to fix half of them. With that said do you think replace would be a better option?
Your method would work, but some of the col2 don't have col1 at the end. I guess that I didn't make that clear. Sorry. Basically we have 2 old sets of data pushed into this new table. So some are:
Col1 Col2
a4 Admin a4
and some are
Col1 Col2
a4 admin
I only need to fix half of them. With that said do you think replace would be a better option?
how about doing it like this then:
<cfif right(queryname.col2,len(q
#trim(left(queryname.col2,
<cfelse>
#queryname.col2#
</cfif>
<cfif right(queryname.col2,len(q
#trim(left(queryname.col2,
<cfelse>
#queryname.col2#
</cfif>
Yup, I think SB has got it.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
rereplacenocase() is probably the best way to do it. get's the job done with the least amount of code.
agreed
ASKER
Thank you, that worked perfectly
#trim(replace(queryname.co