Post reply

removing lettering in front number in field

  • November 21, 2013 at 12:49 pm

    #301568

    Hi Everyone

    I have a field with a mixture of number and letter and I would like to remove all the letter on the left of the field before the first number

    example

    'Fishing lake 123abc' returns '123abc'

    'Elk Hunting trail 876poi' returns '876poi'

    can anyone help me you with this. Thanks.

  • November 21, 2013 at 12:58 pm

    #1668232

    I found it myself

    SELECT

    SUBSTRING([Column Name], PATINDEX('%[0-9]%', [Column Name]), 30)

    will take the 30 characters after the first number found.

  • November 21, 2013 at 1:00 pm

    #1668234

    /*

    (No column name)(No column name)Comment

    14123abcFishing lake 123abc

    19876poiElk Hunting trail 876poi

    */

    ;WITH MyCTE([Comment])

    AS

    (

    SELECT 'Fishing lake 123abc' UNION ALL

    SELECT 'Elk Hunting trail 876poi' UNION ALL

    SELECT 'Non qualifying comment'

    )

    SELECT PATINDEX('%[0-9]%',Comment),

    SUBSTRING(Comment,PATINDEX('%[0-9]%',Comment),30), --assuming only 30 chars or less exist after indexd found

    * FROM MyCTE WHERE PATINDEX('%[0-9]%',Comment) > 0

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

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

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