469,622 Members | 1,462 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,622 developers. It's quick & easy.

PatIndex function

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
Mar 18 '08 #1
3 2554
ck9663
2,878 Expert 2GB
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
Mar 19 '08 #2
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!
Mar 19 '08 #3
ck9663
2,878 Expert 2GB
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:

Expand|Select|Wrap|Line Numbers
  1. declare @IDENTIFICATION varchar(50)
  2.  
  3. set @IDENTIFICATION = '086903426,01,085,K8010,H03,1'
  4.  
  5. select substring(@IDENTIFICATION,charindex(',',@IDENTIFICATION)+1,2)
I just store the value in a variable for presentation.

-- CK
Mar 19 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by John Winterbottom | last post: by
3 posts views Thread by domeceo | last post: by
11 posts views Thread by SQL_developer | last post: by
5 posts views Thread by phil_gg04 | last post: by
2 posts views Thread by laredotornado | last post: by
2 posts views Thread by sushil | last post: by
8 posts views Thread by Olov Johansson | last post: by
3 posts views Thread by Beta What | last post: by
2 posts views Thread by =?Utf-8?B?RGFuaWVs?= | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.