Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Get 1st Word in the string


Get 1st Word in the string

Author
Message
sharonsql2013
sharonsql2013
SSC-Addicted
SSC-Addicted (406 reputation)SSC-Addicted (406 reputation)SSC-Addicted (406 reputation)SSC-Addicted (406 reputation)SSC-Addicted (406 reputation)SSC-Addicted (406 reputation)SSC-Addicted (406 reputation)SSC-Addicted (406 reputation)

Group: General Forum Members
Points: 406 Visits: 1220
I am trying to to extract the 1st word from the string but end up with this error : Invalid length parameter passed to the LEFT or SUBSTRING function.


Query SELECT SUBSTRING(p.Delivery_Site, 1, CHARINDEX(' ', p.Delivery_Site) - 1) AS [First Word]
from [Production.Detail] P

Any Idea how I can fix this .
sharonsql2013
sharonsql2013
SSC-Addicted
SSC-Addicted (406 reputation)SSC-Addicted (406 reputation)SSC-Addicted (406 reputation)SSC-Addicted (406 reputation)SSC-Addicted (406 reputation)SSC-Addicted (406 reputation)SSC-Addicted (406 reputation)SSC-Addicted (406 reputation)

Group: General Forum Members
Points: 406 Visits: 1220
No Worries, Found a Link that helped.

LTRIM(LEFT(p.Delivery_Site, CHARINDEX(' ',p.Delivery_Site)))
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16679 Visits: 17036
sharonsql2013 (4/30/2014)
No Worries, Found a Link that helped.

LTRIM(LEFT(p.Delivery_Site, CHARINDEX(' ',p.Delivery_Site)))


I don't think that will actually work in all situations.


with p as
(
select 'asdf1234' as Delivery_Site union all
select 'asdf 1234' union all
select ' asdf asdf ' union all
select ''
)
select
LTRIM(LEFT(p.Delivery_Site, CHARINDEX(' ',p.Delivery_Site))) --This misses too many things
, LTRIM(LEFT(p.Delivery_Site, LEN(p.Delivery_Site) - CHARINDEX(' ', LTRIM(p.Delivery_Site)))) --This captures the first word
from p



Take a look at this. The first column is what you posted. It returns an empty string for everything except the second value.

--edit--

testing an edit for Steve since somebody reported a bug when editing a post.

_______________________________________________________________

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)
below86
below86
SSChasing Mays
SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)

Group: General Forum Members
Points: 644 Visits: 2135
Sean's version seams to work with the data shown, but if the field is a larger field it doesn't always get the correct results. I used a CASE statement to try and get the correct value. I've included the sample code below. I'm not saying it is 100% correct, but a little closer at least.

For some reason it doesn't like it when I include the SQL.

I had to include it as a text file.

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Attachments
SQLQuery1.txt (8 views, 1.00 KB)
gbritton1
gbritton1
Old Hand
Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)

Group: General Forum Members
Points: 369 Visits: 841
Here's an approach using Chris Morris Pattern Splitter:


select Delivery_Site, firstword.Item
from p
cross apply (
select top 1 * from dbo.PatternSplitCM(Delivery_Site, '%[a-z0-9]%')
where Matched = 1
order by ItemNumber
) firstword



[url=http://www.sqlservercentral.com/articles/String+Manipulation/94365/][/url]
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16679 Visits: 17036
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
, CASE WHEN 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/

_______________________________________________________________

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)
Eirikur Eiriksson
Eirikur Eiriksson
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6842 Visits: 17777
Why not try Jeff Moden's splitter, pass space as delimiter?
Cool
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16679 Visits: 17036
gbritton1 (5/1/2014)
Here's an approach using Chris Morris Pattern Splitter:


select Delivery_Site, firstword.Item
from p
cross apply (
select top 1 * from dbo.PatternSplitCM(Delivery_Site, '%[a-z0-9]%')
where Matched = 1
order by ItemNumber
) firstword



[url=http://www.sqlservercentral.com/articles/String+Manipulation/94365/][/url]


This certainly works but I think the PatternSplit is a bit overkill here.

_______________________________________________________________

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)
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16679 Visits: 17036
Eirikur Eiriksson (5/1/2014)
Why not try Jeff Moden's splitter, pass space as delimiter?
Cool


Try it...


with p AS
(
SELECT 'asdf12345678901' AS Delivery_Site UNION
SELECT 'asd f 234 ' UNION
SELECT ' asdf 345 ' UNION
SELECT ' asdf 234567'
)
select *
from p
cross apply dbo.DelimitedSplit8K(p.Delivery_Site, ' ')



You get a LOT of noise with this. You would still have to left trim everything first. Something like this.


with p AS
(
SELECT 'asdf12345678901' AS Delivery_Site UNION
SELECT 'asd f 234 ' UNION
SELECT ' asdf 345 ' UNION
SELECT ' asdf 234567'
)
, SortedValues as
(
select *
from p
cross apply dbo.DelimitedSplit8K(ltrim(p.Delivery_Site), ' ') x
)

select Item
from SortedValues
where ItemNumber = 1



It certainly works but much like the PatternSplit it is overkill for just some string manipulation. :-)

_______________________________________________________________

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)
below86
below86
SSChasing Mays
SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)SSChasing Mays (644 reputation)

Group: General Forum Members
Points: 644 Visits: 2135
Sean, Your code looses the last value in the string, for value 'asdf12345678901' you loose the 1.

That's wierd I'm having trouble posing code now, Oh well.
Added a 1 + to the substring will take care of that.

SUBSTRING(p.Delivery_Site, 0, 1 + case when CHARINDEX(' ', p.Delivery_Site) = 0 then LEN(p.Delivery_Site) else CHARINDEX(' ', p.Delivery_Site) end) as NewVersion

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search