By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,363 Members | 2,143 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,363 IT Pros & Developers. It's quick & easy.

PatIndex function

P: 7
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
Share this Question
Share on Google+
3 Replies


ck9663
Expert 2.5K+
P: 2,878
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

P: 7
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
Expert 2.5K+
P: 2,878
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.