473,241 Members | 1,543 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,241 software developers and data experts.

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 3650
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Robin Tucker | last post by:
My code prints out 0 for the value of charindex ( 'Hello.', '.' ). Did I miss something? I think it should print out 6!
5
by: M Wells | last post by:
Hi All, Just wondering if it's any more efficient to use the following SQL statement to return a partial match from a column as opposed to the second statement. SELECT * FROM Table1 WHERE...
1
by: sysindex | last post by:
I am trying to find a way to dynamically retrieve the substring starting point of an nText field. My query looks something like SELECT ID,Substring(DOCTEXT,0,200) from mytable where DOCTEXT...
2
by: Little PussyCat | last post by:
Hello, I need to be able to replace only the first occurance of a space character in a column. Reason being is the data in the column I am trying to replace seems to have umpteen space...
3
by: Tim Soliday | last post by:
I have been looking at regular expressions lately, and I'm hoping that maybe someone could help me? I am trying to find the position of a substring, and then returning the substring including...
4
by: Daviso | last post by:
Hi. I hava an input like this <td><input name=numberA> </td> and a button with a javascript function in onclick event. I want to control that the first element of the value I have...
8
by: chudson007 | last post by:
I need help capturing information from a free text field. The 10 examples below contain examples of the information I am trying to capture. In each cell I am trying to capture the number between...
7
by: jknaty | last post by:
I'm trying to create a function that splits up a column by spaces, and I thought creating a function that finds the spaces with CHARINDEX and then SUBSTRING on those values would an approach. I...
1
by: jeremy | last post by:
Hello, I was hoping someone could help me with this SQL statement. The column 'options.optionDescrip' is a varchar field. Some values of the 'options.optionDescrip' contain commas, some do...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.