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

Question

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.

Answer 1

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.