Post reply

Selecting data within data

  • May 13, 2003 at 10:17 am

    #159870

    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

    #457004

    Try taking a look at CHARINDEX and SUBSTRING in BOL.

  • May 13, 2003 at 10:32 am

    #457005

    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

    #457011

    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

    #457012

    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