• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 306
  • 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 BennettManager TechnologyCommented:
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
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
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 BennettManager TechnologyCommented:
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 BennettManager TechnologyCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

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