Post reply

Extracting Numbers from a string

  • September 29, 2004 at 2:26 am

    #163214

    I have a column that contains a string made up of a reference number and a company name. I need to extract the reference number from the string. The problem is that it can appear anywhere in the string.

    The number is always 4 digits. Is there a way I can search the string for the first number that occurs in it, then extract it and the next 3 characters to get the reference number?

    Thanks.

  • September 29, 2004 at 3:54 am

    #524756

    If you are certain that the first number always denotes the start of the reference number, then you can substring it using patindex as start marker and take 4 chars from there...

    declare @string varchar(100)

    set @string = 'First and Second 1234 Avenue'

    select SUBSTRING(@string, PATINDEX('%[0-9]%', @string), 4)

    ----

    1234

    (1 row(s) affected)

    /Kenneth

  • September 29, 2004 at 4:01 am

    #524757

    That's exactly what I needed. Thank you.

Viewing 3 posts - 1 through 3 (of 3 total)

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