String Manipulation

Question

String Manipulation

AndrewSQLDBA

SSC-Insane

Points: 21827
More actions
August 1, 2013 at 2:44 pm

#275843

Hello Everyone
I have a strange thing that I am trying to code for, and I am almost there, but I know there is a function.
I have a string of text that I need to select, which is only the first word. The rest I do not care about. There is a blank space between the two words, there are only two words and I will only need the first word.
Sample:
Irvine Street
I need only the word "Irvine"
I cannot, for the life of me think of the function name. I have tried substring, but that is not doing what I need.
Thank you in advance for your comments, suggestions and assistance
Andrew SQLDBA

Viewing 5 posts - 1 through 4 (of 4 total)

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

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

Something that's not LEFT( myString, CHARINDEX(' ', myString)) ?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 2

Thank You Luis

That is perfect and exactly what I needed, and was trying to do.

I need to brush up on my string manipulations

Thanks again

Andrew SQLDBA

ScottPletcher SSC Guru Points: 100940 More actions · Answer 3

Technically it should be:

LEFT(myString, CHARINDEX(' ', myString + ' ') - 1)

unless you want a trailing space in the result :-).

Adding the "+ ' '" will cause the code to return the complete string if a space is not found.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Alan Burstein SSC Guru Points: 61136 More actions · Answer 4

I put together a little sample code and a few examples (note my comments) for a couple common scenarios

DECLARE @x TABLE (val varchar(20));

INSERT @x VALUES ('two words'),('two words '),('oneword'),(' leading space');

--This will also work (using substring):

SELECTval AS original,

SUBSTRING(val,1,CHARINDEX(' ',val)) AS updated

FROM @x;

--To handle cases where there are no spaces:

SELECTval AS original,

CASE

WHEN CHARINDEX(' ',val)=0 THEN val

ELSE LEFT(val,CHARINDEX(' ',val))

END AS updated

FROM @x;

--To handle cases where there are leading spaces:

SELECTval AS original,

CASE

WHEN CHARINDEX(' ',val)=0 THEN val

ELSE LEFT(LTRIM(val),CHARINDEX(' ',LTRIM(val)))

END AS updated

FROM @x;

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001