Select INTs from start of string separated by a character (pipe). Update other columns using these INTs
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.25update columns T1 and T2 null.
Given the following table:
CREATE TABLE dbo.T ( [Description] varchar(8000) NOT NULL, T1 integer NULL, T2 integer NULL );
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');
The following commented code uses an updatable cursor:
SET XACT_ABORT, NOCOUNT ON; SET STATISTICS XML OFF;
DECLARE DataCursor CURSOR LOCAL
SCROLL DYNAMIC SCROLL_LOCKS
FOR SELECT [Description] FROM dbo.T
FOR UPDATE OF T1, T2;
-- Get the first row
FETCH FIRST FROM DataCursor INTO @Description;
WHILE @@FETCH_STATUS = 0
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;
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
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
TRY_CONVERTmake coding this sort of thing a lot easier.