Link to home
Start Free TrialLog in
Avatar of Westside2004
Westside2004Flag for United States of America

asked on

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
Avatar of knightEknight
knightEknight
Flag of United States of America image

where substring(some_id ,0,len(some_id )-1) in (3,45,256)
... 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)
... 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')
Avatar of Westside2004

ASKER

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
Please clarify -- how can the field be of type int if it contains values like: 256|y ?
Hi,

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

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

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

A different approach would be to insert the passed in values into a separate temporary table and then work with the data that way.
SOLUTION
Avatar of knightEknight
knightEknight
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Glad it helped!