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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/