## Get 1st Word in the string

 Author Message Eirikur Eiriksson SSC Guru Group: General Forum Members Points: 94298 Visits: 20687 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_WORDFROM TXT_INFO TI;`Results`FIRST_WORD-----------asdf1234asdf asdf NULL` Sean Lange SSC Guru Group: General Forum Members Points: 149317 Visits: 18575 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_WORDFROM TXT_INFO TI;`Results`FIRST_WORD-----------asdf1234asdf 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_WORDFROM 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.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)