CHARINDEX problem

Question

Post reply

CHARINDEX problem

Greg Snidow

SSCoach

Points: 16108
More actions
August 18, 2010 at 5:18 pm

#228091

Greetings all. I am having some difficulty getting charindex to work. I have a front end that enters notes into a notes field with format CONVERT(CHAR(10),GETDATE(),101) + ' - ' + User + ':' + 'just some text'. So, if Joe Smith enters a note on August 1st it will be entered as '08/01/2010 - J.Smith: just some note'. I understand the one column should really be three columns, one for the date, one for the user, and one for the note, but it is how it is, and I can't change it. So, now I need to take the notes and pull out the date, user, and note into three columns. The date is easy, because it is always SUBSTRING(note,1,10), and the note is easy, bucause it is always SUBSTRING(note,CHARINDEX(':',note,1),LEN(note)). However, I am having trouble pulling out the user, because regardess of whether I enter 1 or 14 as the start_location of the CHARINDEX function, it always returns the value I would expect if the start_location is 1. The following example should illustrate the trouble I am experiencing.
DECLARE @string VARCHAR(100)
SELECT @string = '08/01/2010 - J.Smith: just some text'
SELECT
[Date] = SUBSTRING(@string,1,10),
[Start at 1] = CHARINDEX(':',@string,1),
[Start at 14] = CHARINDEX(':',@string,14),
[User] = SUBSTRING(@string,14,CHARINDEX(':',@string,14))
DateStart at 1Start at 14User
08/01/20102121J.Smith: just some te
I thought the start_location of the CHARINDEX function would return the distance from the start_location to the desired expression, but it is returning the distance from the start of the string. Am I missing something. I can overcome the problem by using...
[User] = SUBSTRING(@string,14,CHARINDEX(':',@string,1)-14)
but I would still like to understand the behavior of CHARINDEX. Am I missing something?
Thanks
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

Viewing 7 posts - 1 through 6 (of 6 total)

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

prvmine SSCertifiable Points: 7007 More actions · Answer 1

try this

DECLARE @string VARCHAR(100)

select @string = '08/01/2010 - J.Smith: just some text'

SELECT

[Date] = SUBSTRING(@string,1,10),

[Start at 1] = CHARINDEX(':',@string,1),

[Start at 14] = CHARINDEX(':',@string,14),

= substring(@string,(charindex('- ',@string,0)+2),((charindex(':',@string,0)) - (charindex('- ',@string,0)+2)))

Greg Snidow SSCoach Points: 16108 More actions · Answer 2

kschuler-851405 (8/18/2010)
try this
DECLARE @string VARCHAR(100)
select @string = '08/01/2010 - J.Smith: just some text'
SELECT
[Date] = SUBSTRING(@string,1,10),
[Start at 1] = CHARINDEX(':',@string,1),
[Start at 14] = CHARINDEX(':',@string,14),
= substring(@string,(charindex('- ',@string,0)+2),((charindex(':',@string,0)) - (charindex('- ',@string,0)+2)))

Thank you for the quick response. I still don't understand why it does not work as advertised. Take this example...

DECLARE @string VARCHAR(50)

SELECT @string = 'text:more text'

SELECT CHARINDEX(':',@string,1)

SELECT CHARINDEX(':',@string,2)

SELECT CHARINDEX(':',@string,3)

SELECT CHARINDEX(':',@string,4)

SELECT CHARINDEX(':',@string,5)

It returns 5 in each case. I'm flumoxed. It's got to be something simple I am mis-interpreting.

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 3

I still don't understand why it does not work as advertised.

It does work as advertised.

Books Online: Returns the starting position of the specified expression in a character string.

Also, it works similarly (allowing for variations in how the characters are numbered) to the String.indexOf() function in the .NET famework, and the InStr() function in VB6. I'm not aware of any standard library functions in any progamming languages which locate the position of a sub-string in a string that work in the way that you seem to be expecting.

Greg Snidow SSCoach Points: 16108 More actions · Answer 4

Andrew, I think I have mis-interpreted the BOL example, which is as follows...

DECLARE @document varchar(64)

SELECT @document = 'Reflectors are vital safety' +

' components of your bicycle.'

SELECT CHARINDEX('vital', @document, 5)

I took this to mean that when looking for 'vital' starting at position 5, the value of 16 returned meant there were 16 characters between the starting position of 5 and the end of 'vital'. Coincidentally, the math works out to support my mis-assumption in the example. Anyhow, I guess I'll go with my work around. Thanks.

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

prvmine SSCertifiable Points: 7007 More actions · Answer 5

charindex returns the start position of the searched for expression (":"), which is in position 21 of the string in this example; starting at position 14 of the string does not change the positional value where ":" is located

perhaps my response would have been clearer by changing the returned values to these

DECLARE @string VARCHAR(100)

select @string = '08/01/2010 - J.Smith: just some text'

SELECT

[Date] = SUBSTRING(@string,1,10),

[Start] = (charindex('- ',@string,0)+2),

[end] = CHARINDEX(':',@string,14),

[length] = ((charindex(':',@string,0)) - (charindex('- ',@string,0)+2)),

= substring(@string,(charindex('- ',@string,0)+2),((charindex(':',@string,0)) - (charindex('- ',@string,0)+2)))

Greg Snidow SSCoach Points: 16108 More actions · Answer 6

kschuler-851405 (8/18/2010)
charindex returns the start position of the searched for expression (":"), which is in position 21 of the string in this example; starting at position 14 of the string does not change the positional value where ":" is located
perhaps my response would have been clearer by changing the returned values to these
DECLARE @string VARCHAR(100)
select @string = '08/01/2010 - J.Smith: just some text'
SELECT
[Date] = SUBSTRING(@string,1,10),
[Start] = (charindex('- ',@string,0)+2),
[end] = CHARINDEX(':',@string,14),
[length] = ((charindex(':',@string,0)) - (charindex('- ',@string,0)+2)),
= substring(@string,(charindex('- ',@string,0)+2),((charindex(':',@string,0)) - (charindex('- ',@string,0)+2)))

Thank you for the further explanation, it make more sense now. I certainly hope I haven't given any bad advice over the years based on my erroneous assumptions.

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.