• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 303
  • Last Modified:

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.  
0
trifecta2k
Asked:
trifecta2k
  • 4
  • 3
  • 3
  • +2
1 Solution
 
Scott BennettCommented:
you can use replace to remove the value of col1 cfom col2 like:
#trim(replace(queryname.col2,queryname.col2,"","ALL"))#


0
 
trifecta2kAuthor Commented:
just to be sure, should it be col1, then col2?  you have them both listed as col2.  Thanks
0
 
easttncsCommented:
Using your example might use something like this...

<cfset positiontostop = (findoneof('A4', col2) -1)>
<cfset displaythis = mid(col2, 1, positiontostop)>


0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
gdemariaCommented:

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)) )





0
 
gdemariaCommented:
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.
0
 
trifecta2kAuthor Commented:
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?
0
 
Scott BennettCommented:
how about doing it like this then:

<cfif right(queryname.col2,len(queryname.col1)) eq queryname.col1>
#trim(left(queryname.col2,len(queryname.col2)-len(queryname.col1)))#
<cfelse>
#queryname.col2#
</cfif>
0
 
gdemariaCommented:
Yup, I think SB has got it.
0
 
danrosenthalCommented:
You can do this with a regular expression that looks only to see if the code is at the end of the string like this:

#rereplacenocase(queryname.col2,"#queryname.col1#$","")#

The dollar sign means the match only counts if it is at the end of the string.
If there is always a space, you can add that to the lookup for extra security:

#rereplacenocase(queryname.col2," #queryname.col1#$","")#
 
0
 
Scott BennettCommented:
rereplacenocase() is probably the best way to do it. get's the job done with the least amount of code.
0
 
gdemariaCommented:
agreed
0
 
trifecta2kAuthor Commented:
Thank you, that worked perfectly
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now