locked
SELECT only numeric part of string RRS feed

  • Question

  • Hi @ all,

    im forcing a problem where i have a alphanumeric string. I want to make a select where i only select the numeric part of the string. every string starts with letters. I dont want the letters in my select only the digits.

    a way i can do that with a select statement?

     

    Wednesday, February 9, 2011 10:53 AM

Answers

  • Hello,

    try

    select substring([col], PatIndex('%[0-9]%', [col], len([col])) as number from [tab];

    Regards,

    Klaus

    • Marked as answer by BI4beginners Wednesday, February 9, 2011 11:55 AM
    Wednesday, February 9, 2011 11:26 AM

All replies

  • Hi,

    Can you provide some sample input


    Thanks and regards, Rishabh
    Wednesday, February 9, 2011 11:08 AM
  • Hello,

    try

    select substring([col], PatIndex('%[0-9]%', [col], len([col])) as number from [tab];

    Regards,

    Klaus

    • Marked as answer by BI4beginners Wednesday, February 9, 2011 11:55 AM
    Wednesday, February 9, 2011 11:26 AM
  • Hi,

    try

    select substring([col], PatIndex('%[0-9]%', [col], len([col])) as number from [tab];

    I think you haven't close the braces for patindex....


    Thanks and regards, Rishabh
    Wednesday, February 9, 2011 11:32 AM
  • Yes, you are right. Correct is:

    select substring([col], PatIndex('%[0-9]%', [col]), len([col])) as number from [tab];

    Klaus

    Wednesday, February 9, 2011 11:49 AM
  • GREAT!! Thank you guys!

    I had the same thing in a script a couple of months ago but could not find it :-)

     

    • Proposed as answer by tEagleC Thursday, February 22, 2018 3:53 PM
    • Unproposed as answer by tEagleC Thursday, February 22, 2018 3:53 PM
    Wednesday, February 9, 2011 11:54 AM
  • Great answer!! :)


    DVR
    Wednesday, February 9, 2011 12:13 PM
  • What if characters occur on both sides of the number?

    E.g. 123abc456 ? The output would be abc456

    Thursday, April 11, 2019 7:43 AM