SQL server update fields from a concatenated string in a field

Question

I'm fairly new to SQL code which writes to the database. I've been trying to work out this piece of code on my own, but I'm not having a lot of luck. Especially since I really don't know how to test it without actually writing to the DB I have a database with 5 UDF fields. 'UDF1-UDF5'. The operators at my facility are supposed to scan a bar code into a specific bar code field which splits up the bar code into the five fields (they are all char(30) fields ). Unfortunately what is happening is that they are scanning directly into the UDF1 field, so the entire barcode string is all in one field. (I don't have control over this software) I am trying to write a script which will parse the DB, split these fields into separate variables and update the DB. I could use a little assistance because I think I need Dynamic SQL to do this and I don't know much about it. Here is a little more info about the system.

The barcode field looks like this:

%2S12345%1%1%0%10%

where the '%' characters begin and end the bar code and concatenate the characters. the first character of the first UDF field '2' is a check digit, and always the same. The first field is always either 5 or 6 characters (excluding the check digit), the rest are either 1 or 2 digits. I also need code that won't break if the bar code only has the first three fields. Not a lot of consistency here. some of the bar codes are truncated.

questions,

1. As far as I know, the only way to break apart concatenated text is substring() which is position based, so I would need an additional 5 variables to get the length of each field and a way to query that information. Is there an easier way?

2. At some point I have to conditionally set the variables and I can't seem to get set commands to work. I understand why something like this doesn't work, but I don't know any other way of doing it.

.

 DECLARE @BASEID CHAR(30), @LOTID CHAR(30), @SPLITID CHAR(30), @SUBID CHAR(30), @SEQUENCENO CHAR(30), @BASELEN INT
 SET @BASELEN =
    CASE WHEN(
        SELECT ISNUMERIC(SUBSTRING(R.UDF1,3,1))
        FROM VISION17SLITTER.DBO.ROLLINFO R 
        WHERE R.UDF1 LIKE '[%]%'
        ) = 1
        THEN 5
        ELSE 6
    END

3. once I could get the variable set I assume that a simple conditional update statement would work, but if there is anything else I should know before trying this I would appreciate the advice.

Thanks again,

Dan

Answer 1

Create a function to split your string

CREATE FUNCTION [dbo].[fnSplitString] 
( 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(splitdata NVARCHAR(MAX) 
) 
BEGIN 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1

        INSERT INTO @output (splitdata)  
        VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)

    END 
    RETURN 
END

Then remove the first two control characters, and the last control character and invoke the function like this

select * from dbo.fnSplitString('S12345%1%1%0%10','%')

The function will then return a table with the following values:

splitdata
=========
S1234
1
1
0
10

Answer 2

Consider the following:

Declare @YourTable table (ID int,BarCode varchar(100))
Insert Into @YourTable values
(1,'%2S12345%1%1%0%10%'),
(2,'%ABC1234%2%3%4%50%')

Select A.ID
      ,A.BarCode
      ,B.*
 From  @YourTable A
 Cross Apply (
                Select Pos1 = xDim.value('/x[1]','varchar(max)')
                      ,Pos2 = xDim.value('/x[2]','varchar(max)')
                      ,Pos3 = xDim.value('/x[3]','varchar(max)')
                      ,Pos4 = xDim.value('/x[4]','varchar(max)')
                      ,Pos5 = xDim.value('/x[5]','varchar(max)')
                      ,Pos6 = xDim.value('/x[6]','varchar(max)')
                      ,Pos7 = xDim.value('/x[7]','varchar(max)')
                      ,Pos8 = xDim.value('/x[8]','varchar(max)')
                      ,Pos9 = xDim.value('/x[9]','varchar(max)')
                 From (Select Cast('<x>' + Replace(A.BarCode,'%','</x><x>')+'</x>' as XML) as xDim) A

             ) B 

Returns

Now, you may notice Pos1 and Pos7 are blank. This is due to the fact that your string begins and ends with the delimiter. If you want to tailor the CROSS APPLY as such:

Select Pos1 = xDim.value('/x[2]','varchar(max)')
      ,Pos2 = xDim.value('/x[3]','varchar(max)')
      ,Pos3 = xDim.value('/x[4]','varchar(max)')
      ,Pos4 = xDim.value('/x[5]','varchar(max)')
      ,Pos5 = xDim.value('/x[6]','varchar(max)')
 From (Select Cast('<x>' + Replace(A.BarCode,'%','</x><x>')+'</x>' as XML) as xDim) A

Which Returns