Select INTs from start of string separated by a character (pipe). Update other columns using these INTs



  • enter image description here

    In the above screen shot, the Description column has many special characters. We want before | number update in T1 column and | after number in T2 column. Please suggest the proper syntax.

    If there are no before and after | numbers, like the second row | SE +17.5D CYL1.25 update columns T1 and T2 null.



  • Given the following table:

    CREATE TABLE dbo.T
    (
        [Description] varchar(8000) NOT NULL,
        T1 integer NULL,
        T2 integer NULL
    );
    

    and data:

    INSERT dbo.T
        ([Description])
    VALUES
        ('| 30 | 30 | INTERNAL AUDIT | RL | OK'),
        ('| SE+17.5D  CYL 1.25'),
        ('| 10 | 11 | INTERNAL AUDIT | MM | CORRECTED'),
        ('| 5 | 5 | INTERNAL AUDIT | JY | GOOD'),
        ('| 56 | 56 | INTERNAL AUDIT | JMS | OK'),
        ('| 10 | 10 | INTERNAL AUDIT | CN | None'),
        ('| 3 | 3 | INTERNAL AUDIT | MG | GOOD'),
        ('| 46 | 47 | INTERNAL AUDIT | AB | None'),
        ('| 23 | 23 | INTERNAL AUDIT | BA | OK'),
        ('| 30 | 30 | INTERNAL AUDIT | RL | OK'),
        ('| 25 | 29 | INTERNAL AUDIT | KV | CORRECTED'),
        ('| 4 | 3 | INTERNAL AUDIT | KV | PULLING, LIVE AR'),
        ('| ref # - 0006-4121-02');
    

    Solution

    The following commented code uses an updatable cursor:

    SET XACT_ABORT, NOCOUNT ON;
    SET STATISTICS XML OFF;
    

    DECLARE
    @Description varchar(8000),
    @Pipe1Position integer,
    @Pipe2Position integer,
    @T1 integer,
    @T2 integer;

    DECLARE DataCursor CURSOR LOCAL
    SCROLL DYNAMIC SCROLL_LOCKS
    FOR SELECT [Description] FROM dbo.T
    FOR UPDATE OF T1, T2;

    OPEN DataCursor;

    -- Get the first row
    FETCH FIRST FROM DataCursor INTO @Description;

    WHILE @@FETCH_STATUS = 0
    BEGIN
    -- Reset
    SET @T1 = NULL;
    SET @T2 = NULL;

    -- Find the pipe after the first one
    SET @Pipe1Position = CHARINDEX('|', @Description, 2);
    
    IF @Pipe1Position > 0
    BEGIN TRY
        -- Extract the first integer
        SET @T1 = CONVERT(integer, 
            SUBSTRING(@Description, 2, @Pipe1Position - 2));
    
        -- Find the the next pipe
        SET @Pipe2Position = CHARINDEX('|', @Description, @Pipe1Position + 1);
    
        IF @Pipe2Position > 0
        BEGIN
            -- Extract the second integer
            SET @T2 = CONVERT(integer, 
                SUBSTRING(@Description, @Pipe1Position + 1, @Pipe2Position - @Pipe1Position - 1));            
        END;
    END TRY
    BEGIN CATCH
    END CATCH;
    
    -- Perform the update for this row
    UPDATE dbo.T
    SET T1 = @T1, T2 = @T2
    WHERE CURRENT OF DataCursor;
    
    -- Next row
    FETCH NEXT FROM DataCursor INTO @Description;
    

    END;

    CLOSE DataCursor;
    DEALLOCATE DataCursor;

    The final state of the table is:

    Description T1 T2
    | 30 | 30 | INTERNAL AUDIT | RL | OK 30 30
    | SE+17.5D CYL 1.25 NULL NULL
    | 10 | 11 | INTERNAL AUDIT | MM | CORRECTED 10 11
    | 5 | 5 | INTERNAL AUDIT | JY | GOOD 5 5
    | 56 | 56 | INTERNAL AUDIT | JMS | OK 56 56
    | 10 | 10 | INTERNAL AUDIT | CN | None 10 10
    | 3 | 3 | INTERNAL AUDIT | MG | GOOD 3 3
    | 46 | 47 | INTERNAL AUDIT | AB | None 46 47
    | 23 | 23 | INTERNAL AUDIT | BA | OK 23 23
    | 30 | 30 | INTERNAL AUDIT | RL | OK 30 30
    | 25 | 29 | INTERNAL AUDIT | KV | CORRECTED 25 29
    | 4 | 3 | INTERNAL AUDIT | KV | PULLING, LIVE AR 4 3
    | ref # - 0006-4121-02 NULL NULL

    Online demo

    https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=8425325a6e3940eaabb5f69f8004c5b5

    Commentary

    The database is not the best place to this kind of work. The pipe-delimited data looks as if it originated in an external system, and was imported from a flat file.

    Instead of importing the data as-is then processing inside SQL Server, it would be better to use a dedicated ETL tool like SSIS to transform the flat-file data directly then load into one or more relational database tables.

    A cursor won't be ideal if you have tens of millions of rows to process in a hurry, but it does make showing the logic used clear. It performs pretty well all things considered.

    You should also look into getting onto a more modern version of SQL Server. Things like STRING_SPLIT and TRY_CONVERT make coding this sort of thing a lot easier.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2