PatIndex function

[Phokojoe]

I have data items separated by commas (,) in several columns of a table in SQL database. When I use the substring and the patindex function, I get the error that 'Msg 536, Level 16, State 3, Line 1,Invalid length parameter passed to the substring function.' It used to work but now it is not working. What might be the problem

[ck9663]

Would you mind posting what you done so far? Specifically the part of the error and the value of the column that you think the error is happening.

-- CK

[Phokojoe]

For example, I have a column of data in this form:
086903426,01,085,K8010,H03,1 the column name is IDENTIFICATION, I want to take the part before the the first column to say A1 of the second table, the following part which is length 2 i.e. 01 to A2 etc.

This is the script:

SELECT SUBSTRING(INDENTIFICATION,1, PATINDEX('%,%',IDENTIFICATION)-1) AS A1 this works fine cos it takes the 9 digits i.e. 08903426 but this one below complians of the invalid length blah blah

SELECT SUBSTRING(INDENTIFICATION,11, PATINDEX('%,%',IDENTIFICATION)-8) AS A2, I want this to extract only 01 and put it in A2

I hope it explains all.

Ta!

[ck9663]

For example, I have a column of data in this form:
086903426,01,085,K8010,H03,1 the column name is IDENTIFICATION, I want to take the part before the the first column to say A1 of the second table, the following part which is length 2 i.e. 01 to A2 etc.

This is the script:

SELECT SUBSTRING(INDENTIFICATION,1, PATINDEX('%,%',IDENTIFICATION)-1) AS A1 this works fine cos it takes the 9 digits i.e. 08903426 but this one below complians of the invalid length blah blah

SELECT SUBSTRING(INDENTIFICATION,11, PATINDEX('%,%',IDENTIFICATION)-8) AS A2, I want this to extract only 01 and put it in A2

I hope it explains all.

Ta!

Assumption:
The column IDENTIFICATION contain this value '086903426,01,085,K8010,H03,1' and you want to get the "01" in this string.

If the assumption is right, try this:

declare @IDENTIFICATION varchar(50)
 
set @IDENTIFICATION = '086903426,01,085,K8010,H03,1'
 
select substring(@IDENTIFICATION,charindex(',',@IDENTIFICATION)+1,2)

I just store the value in a variable for presentation.

-- CK