Invalid length parameter passed to the SUBSTRING function.

Question

Invalid length parameter passed to the SUBSTRING function.

vwilliams

SSC-Addicted

Points: 435
More actions
September 22, 2010 at 9:40 am

#100308

Can anyone see what is wrong with this statement? I have this same code in other scripts and it does not give an error.
[/quote]
update ds_CleanNames
set clean_name1 = substring(clean_name1, 0, (PATINDEX('% DBA %', clean_name1))),
clean_Extra_Name = substring(clean_name1, (PATINDEX('% DBA %', clean_name1)+5), len(clean_name1)-(PATINDEX('% DBA %', clean_name1)+4))
where clean_name1 like '% DBA %';

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Kingston Dhasian SSCoach Points: 19795 More actions · Answer 1

You probably have some data in your column clean_name1 which is like say 'John is a DBA '. In this case you will get the value for len(clean_name1)-(PATINDEX('% DBA %', clean_name1)+4) as -1 which will give you an error.

Use the below query to get the problematic records

SELECT clean_name1, len(clean_name1)-(PATINDEX('% DBA %', clean_name1)+4)

FROM ds_CleanNames

WHERE clean_name1 like '% DBA %'

AND len(clean_name1)-(PATINDEX('% DBA %', clean_name1)+4) < 0;

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

vwilliams (9/22/2010)
Can anyone see what is wrong with this statement?

what kind of error you are getting ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Looks like Kingston's got the nail on the head here.

As a matter of interest, what is the aim of this operation?

The first action grabs everything in a string to the left of ' DBA '.

The second action takes everything to the right of ' DBA '.

What happens next?

Have a look at REVERSE() for an alternative method of performing action 2.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

vwilliams SSC-Addicted Points: 435 More actions · Answer 4

It is taking the part before the DBA as the first field.

It is taking everything after the DBA and populating another field.

I am cleaning the fields to do a match against our database.

Neither field should contain 'DBA'

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Thanks for clarification. Here's a little added value...

DROP TABLE #ds_CleanNames

CREATE TABLE #ds_CleanNames (clean_name1 VARCHAR(20), Clean_Extra_Name VARCHAR(20))

INSERT INTO #ds_CleanNames (clean_name1)

SELECT 'AB DBA CD' UNION ALL

SELECT 'AB DBA CD EF DBA GH' UNION ALL

SELECT ' DBA CD EF DBA GH' UNION ALL

SELECT 'John is a DBA ' UNION ALL

SELECT 'John is a DBA'

-- '[' reveals spaces which you may not want

SELECT *,

leftpart = '[' + LEFT(clean_name1, CHARINDEX(' DBA ', clean_name1)) + ']',

rightpart = '[' + REVERSE(LEFT(REVERSE(clean_name1), CHARINDEX(' ABD ', REVERSE(clean_name1)))) + ']',

AllExcept = REPLACE(clean_name1, ' DBA ', '.') -- < --- operates on all occurrences of ' DBA '

FROM #ds_CleanNames

WHERE clean_name1 like '% DBA %';

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

vwilliams SSC-Addicted Points: 435 More actions · Answer 6

I ran with the below script and got an error: Incorrect syntax near the keyword 'where'.

I am loving the change though.

[/quote]

,

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

vwilliams (9/24/2010)
I ran with the below script and got an error: Incorrect syntax near the keyword 'where'.
I am loving the change though.

,[/quote]

There's an extra comma and a missing right paren in your code, try this:

update ds_CleanNames set

clean_name1 = LEFT(clean_name1, CHARINDEX(' DBA ', clean_name1)),

clean_Extra_Name = REVERSE(LEFT(REVERSE(clean_name1), CHARINDEX(' ABD ', REVERSE(clean_name1))))

where clean_name1 like '% DBA %'

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

David Webb-CDS SSCoach Points: 17398 More actions · Answer 8

Remove the last comma on the line above the 'where'.

And then again, I might be wrong ...
David Webb

vwilliams SSC-Addicted Points: 435 More actions · Answer 9

That was it Chris. Thanks. This code is actually cleaner than what I was using. Less chance of error.

Thanks so much