CHARINDEX()

Question

CHARINDEX()

Viewing 8 posts - 16 through 22 (of 22 total)

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

Calibear Ten Centuries Points: 1377 More actions · Answer 1

Thanks for all the tips. This is my first attempt at a Question of the Day, so I will definitely take all your suggestions into account (including pointing to 2012 documentation instead of 2005).

--------
For SQL Tips, check out my blog: http://lantztechknowledge.blogspot.com/[/url]
You can also follow my twitter account to get daily updates: @BLantz2455

Dineshbabu Hall of Fame Points: 3220 More actions · Answer 2

Calibear (5/21/2013)
Thanks for all the tips. This is my first attempt at a Question of the Day, so I will definitely take all your suggestions into account (including pointing to 2012 documentation instead of 2005).

It's very good job for Day one.. Keep it up..

--
Dineshbabu
Desire to learn new things..

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Straight forward question - thanks

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Neeraj Prasad Sharma Ten Centuries Points: 1315 More actions · Answer 4

memory refreshed

Neeraj Prasad Sharma
Sql Server Tutorials

Neeraj Prasad Sharma Ten Centuries Points: 1315 More actions · Answer 5

Neeraj Prasad Sharma

Ten Centuries

Points: 1315

May 27, 2013 at 3:55 am

#1618726

Thanx

1+

Neeraj Prasad Sharma
Sql Server Tutorials

manik_anu SSCrazy Points: 2377 More actions · Answer 6

nice basic question....:-)

Manik
You cannot get to the top by sitting on your bottom.

sqlnaive SSCoach Points: 17437 More actions · Answer 7

sqlnaive

SSCoach

Points: 17437

May 31, 2013 at 4:31 am

#1620001

Did it little fast and smiled thereafter. 🙂

marlon.seton SSCrazy Points: 2623 More actions · Answer 8

Hugo Kornelis (5/21/2013)
Mighty (5/21/2013)
Hi, this was a nice question, however I don't fully agree with the wording
For Smith, we don't substring it, since S is the first character
SUBSTRING is still used, but since it starts at the first character of the string (start expression < 1), and the length expression is longer than the length of the string, the whole string is returned.
Yup, with one small caveat.
As palotaiarpad says, the question is actually more about SUBSTRING than about CHARINDEX. For the Smith row (and assuming default collations - I am glad someone else pointed that out this time, as I start to grow tired of repeating myself), the CHARINDEX() function returns 1, subtract 1 and the result is 0. The effect of any start position below 1 is that SUBSTRING will return characters from the start of the string, but reduce the effective length by the difference between the start position and 1. So in this case, for Smith the effective SUBSTRING arguments are SUBSTRING ('Smith', 0, 10), which is equivalent to SUBSTRING('Smith', 1, 9). Since Smith has less than 9 characters, all of it is returned.
Try the same code with 'Smithsonain', and you *will* note the difference between a start position of 0 or 1 with a length 10.
Reference: http://msdn.microsoft.com/en-US/library/ms187748%28v=sql.110%29.aspx.

I agree with the previous poster that the available answers made the selection of the right one too easy.

Thanks to Hugo for the extra info. I didn't know starting before 1 reduced the length of the substring.