CHARINDEX - CAPTURING TEXT TO LEFT

Question

CHARINDEX - CAPTURING TEXT TO LEFT

Kenena Klosson

SSC Eights!

Points: 958
More actions
July 31, 2007 at 3:15 pm

#120893

Hello -
Is there any way to capture all the text LEFT of the CHARINDEX character in a text string, dropping everything on the right?
I've been reading through everything for days and days. If it's not possible, could anyone please explain why it is not possible in understandable format for newbie?
Thanks,

Viewing 9 posts - 1 through 8 (of 8 total)

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

John Rowan SSC Guru Points: 56440 More actions · Answer 1

There are a number of methods you could use to do this including, but necessarily limited to, the following example. All three of these methods are well documented in BOL. Where have you been reading for days and days? Look up 'String Functions' in BOL for all functions that work with string values.

DECLARE @Table TABLE (RowNum int IDENTITY(1,1), Value varchar(200))

INSERT INTO @Table (Value)

SELECT 'This is a substring test'

SELECT 'SUBSTRING: ' as Method, SUBSTRING(Value,1,CHARINDEX('substring', Value)-1)

FROM @Table

UNION ALL

SELECT 'LEFT: ', LEFT(Value,CHARINDEX('substring', Value)-1)

FROM @Table

UNION ALL

SELECT 'STUFF: ', STUFF(Value,CHARINDEX('substring', Value),LEN(Value),'')

FROM @Table

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Sergiy SSC Guru Points: 110208 More actions · Answer 2

John,

to avoid errors when pattern cannot be found in string use this:

CASE WHEN CHARINDEX('substring', Value) = 0

THEN Value

ELSE LEFT(Value,CHARINDEX('substring', Value)-1)

END

_____________
Code for TallyGenerator

John Rowan SSC Guru Points: 56440 More actions · Answer 3

Yep, you're correct. Thanks for the catch

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Kenena Klosson SSC Eights! Points: 958 More actions · Answer 4

Thanks, Sergi and John -

I'll try this with my data.

Kenena

Kenena Klosson SSC Eights! Points: 958 More actions · Answer 5

Thanks for your help, but I am receiving the error message that data type of TEXT is invalid for the function

The comments field is like the following:

TOTAL USAGE READING NEEDED FOR PM GENERATION "SYSTEM CHECKS,INSPECTIONS and OPERATION" *Engine undergoing 40,000hour in frame overhaul.

I can change the * to another character, but I would like to first know I can get ONLY what is left of the *. Any help would be appreciated. Also Sergi - where would the CASE statement go?

Thanks,Kenena

Sergiy SSC Guru Points: 110208 More actions · Answer 6

For text datatype you need to use PATHINDEX instead of CHARINDEX and SUBSTRING instead of LEFT.

Check Books Online for the syntax.

CASE goes right after SELECT.

_____________
Code for TallyGenerator

David Burrows SSC Guru Points: 65142 More actions · Answer 7

The use of

SUBSTRING([textcolumn],1,PATINDEX('%*%',[textcolumn]))

will not produce an error if * is not in the data but would produce erronous data depending on your view of 'erroneous'

If an empty string is required then the CASE is not required but if the whole data is required then the CASE (as in Sergiy's answer) is required

Far away is close at hand in the images of elsewhere.
Anon.

David Burrows SSC Guru Points: 65142 More actions · Answer 8

p.s. the above in SQL2K will return a maximum of 8000 chars (the limit for varchar)

Far away is close at hand in the images of elsewhere.
Anon.