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

  • 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, 2 months ago by  tim8w.
  • 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;

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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