Post reply

Need to strip off the beginning of a field in SELECT statement

  • January 22, 2021 at 4:27 pm

    #3836348

    I have this table that unfortunately has spaces in the field, which I am trimming using LTRIM(RTRIM(Field)) on. In addition, sometimes the field has "1101" at the beginning of the Field. Is there a way to remove it if it's there?

    Current SELECT statement:

    SELECT LTRIM(RTRIM(Field)) FROM myTable

    Example field values:

    1101BRZ

    INSP

    etc.

    • This topic was modified 3 years, 10 months ago by  tim8w.

  • January 22, 2021 at 4:48 pm

    #3836362

    Something like this?

    CREATE TABLE #SomeValues (SomeData VARCHAR(50));

    INSERT #SomeValues (SomeData)
    VALUES
    ('1101BRZ')
    ,('BRZ')
    ,('    1101BRZ')
    ,('   BRZ    1101')
    ,('    INSP');

    SELECT
        sv.*
    ,   Cleaned = IIF(t.trimmed LIKE '1101%', STUFF(t.trimmed, 1, 4, ''), t.trimmed)
    FROM #SomeValues sv
        CROSS APPLY
    (SELECT trimmed = LTRIM(RTRIM(sv.SomeData))) t;

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • January 23, 2021 at 12:26 am

    #3836494

    Thanks! That helps a lot

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

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