Strip Characters? Left() ??

How can I strip characters in SQL?

I have this code in my where statement

WHERE some_id IN (4|y,5|y,6|y)

I want to remove the |y or |n, basically its "y" for yes and "n" for no, but I need to remove that if possible.. so I am left with:

WHERE some_id IN (4,5,6).  It will always have that structure.  In some cases the id might be 256|n, 257|y so its not just 1 digit to the left of the pipe delimiter

Any help appreciated.

-ws
LVL 1
Westside2004Asked:
Who is Participating?
 
usachrisk1983Commented:
While I'm a fan of letting the DB do most of the work, I think in this case you need to let CF parse them.

Something like this:
<cfset variables.myVar = "4|y,5|y,6|y">
<cfset variables.newVar = "">
<cfloop index="variables.i" from="1" to="#ListLen(variables.myVar)#">
 <cfset variables.value = ListGetAt(ListGetAt(variables.myVar,variables.i),1,'|')>
 <cfset variables.newVar = ListAppend(variables.newVar,variables.value)>
</cfloop>
<cfoutput>#variables.newVar#</cfoutput>

Replace variables.myVar with the form variable.
0
 
knightEknightCommented:
where substring(some_id ,0,len(some_id )-1) in (3,45,256)
0
 
knightEknightCommented:
... the above code will strip off the last two characters (either '|y' or '|n') of the some_id field (assuming it is a char or varchar field)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
knightEknightCommented:
... which reminds me, if the field is char or varchar then the IN clause should be too:

where substring(some_id,0,len(some_id )-1)  in  ('3', '45', '256')
0
 
Westside2004Author Commented:
HI,

The field is of type INT.  I still get an error with this

      where substring(someIntColumn ,0,len(someIntColumn )-1) in (3,4,5,255)

The error is:

Invalid column name 'y'.

-ws
0
 
knightEknightCommented:
Please clarify -- how can the field be of type int if it contains values like: 256|y ?
0
 
Westside2004Author Commented:
Hi,

The field does not contain these values, they are getting passed like this from a form, I need to strip them out.
0
 
knightEknightCommented:
oh -- so it is the values in the IN clause that you need to strip -- not the values in the column?

0
 
knightEknightCommented:
kind of kludgy, but for a first pass this works:

  where some_id  in  ( substring('3|y',0,len('3|y')-1), substring('45|y',0,len('45|y')-1), substring('256|y',0,len('256|y')-1) )

0
 
knightEknightCommented:
I assume that the values will be passed in as parameters, in which case the syntax would be:

  where some_id  in  ( substring(@param1,0,len(@param1)-1), substring(@param2,0,len(@param2)-1), substring(@param3,0,len(@param3)-1) )

0
 
knightEknightCommented:
A different approach would be to insert the passed in values into a separate temporary table and then work with the data that way.
0
 
knightEknightCommented:
BTW - I originally viewed this question thru the SQL Server forum, which is why I am focusing on that type of solution.  It may be better to strip away the last two characters in CF before you put them in the SQL statement.
0
 
Westside2004Author Commented:
Yes, that did the trick, the temp table was an idea I was thinking of too.  Thanks both of you for the help.  CF was the way to go here I think I just was not sure how to code that out.

-ws
0
 
usachrisk1983Commented:
Glad it helped!
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.

All Courses

From novice to tech pro — start learning today.