Posted by Gert-Jan Strik on 10/02/57 11:48

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

• Next in forum: Re: How to make a correct select
• Prev in forum: Re: Question on Transaction Log Backups
• Thread view: Re: Substring and Charindex Script Not Working

[Reply to this message]