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 ««12

Get 1st Word in the string Expand / Collapse
Author
Message
Posted Thursday, May 1, 2014 11:54 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:33 PM
Points: 1,216, Visits: 3,550
A simple solution

with p as
(
select 'asdf1234' as Delivery_Site union all
select 'asdf 1234' union all
select ' asdf asdf ' union all
select ''
)
,TXT_INFO AS
(
SELECT
LTRIM(PP.Delivery_Site) AS Delivery_Site
,CHARINDEX(' ',PP.Delivery_Site) AS HAS_SPACE
,LEN(LTRIM(PP.Delivery_Site)) AS HAS_CHAR
FROM p PP
)
SELECT
CASE
WHEN TI.HAS_CHAR > 0 AND TI.HAS_SPACE > 0 THEN SUBSTRING(TI.Delivery_Site,1,CHARINDEX(' ',TI.Delivery_Site))
WHEN TI.HAS_CHAR > 0 AND TI.HAS_SPACE = 0 THEN TI.Delivery_Site
ELSE NULL
END AS FIRST_WORD
FROM TXT_INFO TI;

Results
FIRST_WORD
-----------
asdf1234
asdf
asdf
NULL
Post #1566812
Posted Thursday, May 1, 2014 12:23 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 13,062, Visits: 11,891
Eirikur Eiriksson (5/1/2014)
A simple solution

with p as
(
select 'asdf1234' as Delivery_Site union all
select 'asdf 1234' union all
select ' asdf asdf ' union all
select ''
)
,TXT_INFO AS
(
SELECT
LTRIM(PP.Delivery_Site) AS Delivery_Site
,CHARINDEX(' ',PP.Delivery_Site) AS HAS_SPACE
,LEN(LTRIM(PP.Delivery_Site)) AS HAS_CHAR
FROM p PP
)
SELECT
CASE
WHEN TI.HAS_CHAR > 0 AND TI.HAS_SPACE > 0 THEN SUBSTRING(TI.Delivery_Site,1,CHARINDEX(' ',TI.Delivery_Site))
WHEN TI.HAS_CHAR > 0 AND TI.HAS_SPACE = 0 THEN TI.Delivery_Site
ELSE NULL
END AS FIRST_WORD
FROM TXT_INFO TI;

Results
FIRST_WORD
-----------
asdf1234
asdf
asdf
NULL


Nice! I tweaked it one step further to make it a little simpler.

with p as
(
SELECT 'asdf12345678901' AS Delivery_Site UNION
SELECT 'asd f 234 ' UNION
SELECT ' asdf 345 ' UNION
SELECT ' asdf 234567' union all
select ' ' union all
select ''
)
,TXT_INFO AS
(
SELECT
LTRIM(PP.Delivery_Site) AS Delivery_Site
,CHARINDEX(' ', ltrim(PP.Delivery_Site)) AS HAS_SPACE
FROM p PP
)
SELECT *,
CASE
WHEN TI.HAS_SPACE > 0 THEN SUBSTRING(TI.Delivery_Site, 1, HAS_SPACE)
else nullif(TI.Delivery_Site, '')
END AS FIRST_WORD
FROM TXT_INFO TI;



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1566821
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse