Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

String Manipulation Expand / Collapse
Author
Message
Posted Thursday, August 1, 2013 2:44 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, July 17, 2014 3:19 PM
Points: 975, Visits: 3,342
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
Post #1480148
Posted Thursday, August 1, 2013 2:53 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:52 PM
Points: 3,325, Visits: 7,172
Something that's not LEFT( myString, CHARINDEX(' ', myString)) ?


Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1480154
Posted Thursday, August 1, 2013 2:59 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, July 17, 2014 3:19 PM
Points: 975, Visits: 3,342
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
Post #1480160
Posted Thursday, August 1, 2013 3:09 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:20 PM
Points: 1,959, Visits: 2,893
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1480162
Posted Thursday, August 1, 2013 3:27 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 12:56 PM
Points: 553, Visits: 2,575
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):
SELECT val AS original,
SUBSTRING(val,1,CHARINDEX(' ',val)) AS updated
FROM @x;

--To handle cases where there are no spaces:
SELECT val 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:
SELECT val AS original,
CASE
WHEN CHARINDEX(' ',val)=0 THEN val
ELSE LEFT(LTRIM(val),CHARINDEX(' ',LTRIM(val)))
END AS updated
FROM @x;



-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1480172
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse