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 1 month 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 the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Thanks! That helps a lot

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

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