469,266 Members | 2,063 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

Substring and Charindex Script Not Working

This script doesn't work. Why?

UPDATE [460zz_Relma_Comment_Parse]
SET [Comments 1] = LTRIM(SUBSTRING([Comments], 1,
convert(bigint, CHARINDEX(',', Comments)-1)))
WHERE NOT([COMMENTS] IS NULL) AND LEN([Comments]) > 8

Basically, I'm trying to move everything before a comma into [Comments
1] and everything after the comma into [Comments 2]. The comments 2
script works. See below.

UPDATE [460zz_Relma_Comment_Parse]
SET [Comments 2] = LTRIM(SUBSTRING([Comments], convert(bigint,
charindex(',', [Comments])+1), Len([Comments]) - charindex(',',
[Comments])))
WHERE NOT([COMMENTS] IS NULL) AND LEN([Comments]) > 8

This script [Comments 2] runs perfectly but the first one [Comments 1]
above gives me the following error.

Msg 536, Level 16, State 5, Line 1
Invalid length parameter passed to the SUBSTRING function.
The statement has been terminated.

They are basically the same script except I'm adding +1 to one script
and minusing one -1 from the other.

Thanks,

db55

May 24 '06 #1
1 3411
db55,

You probably have rows where there is a Comment (of at least 8
characters) that has no ',' in it, as in the following example:

SELECT LTRIM(SUBSTRING(Comments, 1, convert(bigint, CHARINDEX(',',
Comments)-1)))
FROM (
SELECT 'this is just an example' AS Comments
) AS "460zz_Relma_Comment_Parse"
WHERE NOT(COMMENTS IS NULL) AND LEN(Comments) > 8
-- AND Comments LIKE '%,%'

Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.

HTH,
Gert-Jan

db55 wrote:

This script doesn't work. Why?

UPDATE [460zz_Relma_Comment_Parse]
SET [Comments 1] = LTRIM(SUBSTRING([Comments], 1,
convert(bigint, CHARINDEX(',', Comments)-1)))
WHERE NOT([COMMENTS] IS NULL) AND LEN([Comments]) > 8

Basically, I'm trying to move everything before a comma into [Comments
1] and everything after the comma into [Comments 2]. The comments 2
script works. See below.

UPDATE [460zz_Relma_Comment_Parse]
SET [Comments 2] = LTRIM(SUBSTRING([Comments], convert(bigint,
charindex(',', [Comments])+1), Len([Comments]) - charindex(',',
[Comments])))
WHERE NOT([COMMENTS] IS NULL) AND LEN([Comments]) > 8

This script [Comments 2] runs perfectly but the first one [Comments 1]
above gives me the following error.

Msg 536, Level 16, State 5, Line 1
Invalid length parameter passed to the SUBSTRING function.
The statement has been terminated.

They are basically the same script except I'm adding +1 to one script
and minusing one -1 from the other.

Thanks,

db55

May 24 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by M Wells | last post: by
1 post views Thread by sysindex | last post: by
2 posts views Thread by Little PussyCat | last post: by
3 posts views Thread by Tim Soliday | last post: by
4 posts views Thread by Daviso | last post: by
8 posts views Thread by chudson007 | last post: by
1 post views Thread by jeremy | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.