Post reply

functions in SQL 2000...similar to INSTR and SUBSTR in Oracle

  • May 6, 2008 at 7:06 am

    #185096

    Hi,

    I have a situation where i need to split an email address with "@".

    Lets say i want to split "abc@def.com" into two strings "abc" and "def.com". To achieve the above i do the following in Oracle.

    [input_str is "abc@def.com"]

    IF (INSTR(input_str,'@') > 1) THEN

    startStr:=SUBSTR(input_str,1,INSTR(input_str,'@')-1);

    endStr:=SUBSTR(input_str,INSTR(input_str,'@')+1);

    I need to do the same process in SQL 2000. Need your help.

    Thanks

    Roy

  • May 6, 2008 at 10:06 am

    #811315

    Here's what I came up with. Someone else might have a better method.

    if CHARINDEX('@',@input_str,1) > 1

    set @startstr = SUBSTRING(@input_str,1, PATINDEX('%@%',@input_str)- 1)

    set @endstr = RIGHT(@input_str,LEN(@input_str) - PATINDEX('%@%',@input_str))

    Greg

  • May 6, 2008 at 12:22 pm

    #811396

    left(string, charindex('@', string, 0)-1

    will give you everything to the left of the "@"

    right(string, len(string) - charindex('@', string, 0))

    will give you everything to the right of the "@".

    Charindex finds the position of a substring inside a string.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • May 6, 2008 at 11:15 pm

    #811571

    Thanks all....The solution really helped in time.

  • May 13, 2008 at 1:43 am

    #814278

    I have an extra twist in my requirements and need some help. In my case, the substring that I need to find (the delimiter) is repeated an unknown number of times. I want everything to the right of the next-to-last delimiter. Look at the examples below. To the left of the arrow is the input string. To the right of the arrow, in quotes, is my desired output.

    I.Want.A.Substring.Based ====> "Substring.Based"

    On.The.End.Of.The.String ====> "The.String"

    With.an.unknown.length.and.unknown.number.of.delimiters ====> "of.delimiters"

    Oracle INSTR would make this easy, but I am not sure how to do it in SQL 2005. I want to avoid UDFs for this, and think I ought to be able to do it in a select with some nesting of functions.

    Can one of you guys point me in the right direction?

    Thanks,

    Bill

  • May 13, 2008 at 2:21 am

    #814287

    Perhaps start by reversing the string (REVERSE). From there it should be easy to get 2 delimiters into the string with CHARINDEX and it's optional StartPosition parameter.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply