Good catch below86. The introduction of multiple spaces really messes up that code I posted.
Some people do have problems posting code. It is often their firewall that prevents it. At any rate here is the code you posted.
with p AS
(
SELECT 'asdf1234' as Delivery_Site UNION all
SELECT 'asdf 1234' UNION all
SELECT ' asdf asdf ' UNION all
SELECT ''
)
SELECT p.Delivery_Site,
LTRIM(LEFT(p.Delivery_Site, CHARINDEX(' ',p.Delivery_Site))) AS Yours --This misses too many things
, LTRIM(LEFT(p.Delivery_Site, LEN(p.Delivery_Site) - CHARINDEX(' ', LTRIM(p.Delivery_Site)))) AS Seans_vers --This captures the first word
, CASEWHEN CHARINDEX(' ', LTRIM(p.Delivery_Site)) = 0
THEN p.Delivery_Site
ELSE
LEFT(LTRIM(p.Delivery_Site), CHARINDEX(' ', LTRIM(p.Delivery_Site)))
END AS My_version
FROM p
This made me wonder if we could just deal with the multiple spaces first and save some effort. Not sure this is actually easier but since it does work correctly I will share.
with p AS
(
SELECT 'asdf12345678901' AS Delivery_Site UNION
SELECT 'asd f 234 ' UNION
SELECT ' asdf 345 ' UNION
SELECT ' asdf 234567'
)
, CleanedVersion as
(
select LTRIM(RTRIM(
REPLACE(REPLACE(REPLACE(Delivery_Site,' ',' þ'),'þ ',''),'þ','')
)) collate Latin1_General_CI_AI as Delivery_Site
from p
)
select *
, SUBSTRING(p.Delivery_Site, 0, case when CHARINDEX(' ', p.Delivery_Site) = 0 then LEN(p.Delivery_Site) else CHARINDEX(' ', p.Delivery_Site) end) as NewVersion
from CleanedVersion p
The second cte named CleanedVersion here is using a technique by Jeff Moden for removing multiple spaces in a single pass instead of a loop. You can read about that technique here. http://www.sqlservercentral.com/articles/T-SQL/68378/[/url]
_______________________________________________________________
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/