May 13, 2003 at 10:17 am
Hi All,
This will probably be an easy one. Im trying to select data within data
i.e. I have a column containing values such as 1111-2222-3333, 234-4565465-89, etc..
What I want to do is only SELECT the values between the two '-'. So for the 2 fields above i would get 2222 and 4565465.
How would I write this query? Thanks for the help!
May 13, 2003 at 10:30 am
Try taking a look at CHARINDEX and SUBSTRING in BOL.
May 13, 2003 at 10:32 am
I did but the problem with that is that it also requires a length and as you can see from the two sets above, the length will vary..unless there is someway to specify an end terminator and i can specify the second '-'..
May 13, 2003 at 11:47 am
I think the charindex function can be used to compute varying lengths...something like this :
Select Substring(@X,charindex('-',@X),Charindex('-',@X,Charindex('-',@X)+1)-charindex('-',@X))
a little messy - I think there should be a better way to do this...
basically what this does is calcuate the position of occurance of the first "-" then from this position go till the position of the next "-" and get the substring of the string that lies within the positions of the two "-"
May 13, 2003 at 12:14 pm
Thanks winash! I actually just modified it a little since it captured the first '-' in the result set:
Select Substring(email,charindex('-',email)+1,Charindex('-',email,Charindex('-',email)+1)-charindex('-',email)-1)
from matttest
That removes both '-' from the results..thanks again!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply