Post reply

parsing data

  • January 26, 2006 at 10:34 am

    #90521

    Hi i need a help in parsing out data from a particular field and placing it into another field. what i have is

    xxxxxxxxxxxxxx(xxx-xxx-xxx-120-xxx)

    xxxxxxxxxxxxxxxxxx(xxx-xxx-xxx-xxx-455-x)

    I have data some what like this with some description in place of 'X' what i want is i need to pull that three digit number into a separate field. Since i have large data with the diffrent lengths, i am not getting any idea as to how to pull that three digit number. Any help is appreciated.

    Thanks 

     

  • January 26, 2006 at 10:37 am

    #617253

    Need more information.

    are the characters you indicate by an x, are they always Alpha characters? not numbers, if so its easy.

    If not, then how would you know what numbers to pull out.

    Pleas let us know.

  • January 26, 2006 at 10:40 am

    #617255

    They are all characters but in some rows will be having only one number. i mean xxxxxxxxxxxxxxxx(xxx-5xx-xxxx-128-). something like this.

  • January 26, 2006 at 10:42 am

    #617257

    i just need to pull that three digit number. from my example above i need to pull 128

  • January 26, 2006 at 11:18 am

    #617269

    Nevermind,

    its still very easy.

    -- drop table #t1

    create table #t1 (pk int identity, Field varchar(100))

    insert into #t1 (Field)

    select 'xxxxxxxxxxxxxx(xxx-xxx-xxx-120-xxx)' union

    select 'xxxxxxxxxxxxxxxxxx(xxx-xxx-xxx-xxx-455-x)' union

    select 'xxxxxxxxxxxxxxxx(xxx-5xx-xxxx-128-).'union

    select 'xxxxx66xxxxxxxx(x6x-5xx-5xxx-333-)' union

    select 'xxxxx66xxxxxxxx(x6x-5xx-5xxx-023-)'

    select substring(Field,patindex('%[0-9][0-9][0-9]%', Field),3)

    from #t1

    Returns

    023

    333

    120

    128

    Note this only pulls numbers where there are 3 consecutive numbers.

    So if any row has 3 consecutive numbers that are not the number you want it will fail.

     

  • January 26, 2006 at 11:53 am

    #617280

    Hey thanks a lot for your solution. It worked fine. but have one more question. if i dont have any numbers in the data ex: i just have PAxxxx(xxx-xxx-xxx). Then its returning first two characters. The output i am getting is PA. Its working fine if i have numbers in there. is there any way that i can mention that row that has got only characters as either null or 0. Anyways Thank u so much for ur quick reply

  • January 26, 2006 at 12:10 pm

    #617290

    Oh, just put in your where clause

     

    select substring(Field,patindex('%[0-9][0-9][0-9]%', Field),3)

    from #t1

    where patindex('%[0-9][0-9][0-9]%', Field) > 0

     

    cheers

  • January 26, 2006 at 1:08 pm

    #617304

    hey!!!!!!!! that worked thank u so much for ur help.

Viewing 8 posts - 1 through 7 (of 7 total)

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