Westside2004
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
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
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(so me_id )-1) in ('3', '45', '256')
where substring(some_id,0,len(so
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
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 ?
ASKER
Hi,
The field does not contain these values, they are getting passed like this from a form, I need to strip them out.
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('2 56|y')-1) )
where some_id in ( substring('3|y',0,len('3|y
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(@p aram1)-1), substring(@param2,0,len(@p aram2)-1), substring(@param3,0,len(@p aram3)-1) )
where some_id in ( substring(@param1,0,len(@p
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
-ws
Glad it helped!