Home Forums SQL Server 7,2000 T-SQL Trimming Last 3 Numbers from some Records RE: Trimming Last 3 Numbers from some Records

  • Here is another method, find the last character which is not space or numeric

    😎

    USE tempdb;

    GO

    ;WITH SAMPLE_DATA(ID,Column1) AS

    (SELECT ID,Column1 FROM

    (VALUES

    (1,'ABCD 123')

    ,(2,'XY 567')

    ,(3,'ghj 001')

    ,(4,'RUT')

    ,(5,'PUT')) AS X(ID,Column1)

    )

    ,T(N) AS (SELECT N FROM ( VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))

    SELECT

    SD.ID

    ,SUBSTRING(SD.Column1,1,MAX(NM.N)) AS CLEAN_COL

    FROM SAMPLE_DATA SD

    CROSS APPLY

    (SELECT TOP(LEN(SD.Column1)) (LEN(SD.Column1) + 1) - ROW_NUMBER()

    OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7) AS NM(N)

    WHERE ASCII(SUBSTRING(SD.Column1,NM.N,1)) NOT IN (32,48,49,50,51,52,53,54,55,56,57)

    GROUP BY SD.ID,SD.Column1;

    Results

    ID CLEAN_COL

    ----------- ---------

    1 ABCD

    2 XY

    3 ghj

    4 RUT

    5 PUT