April 30, 2014 at 12:48 pm
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 .
April 30, 2014 at 12:53 pm
No Worries, Found a Link that helped.
LTRIM(LEFT(p.Delivery_Site, CHARINDEX(' ',p.Delivery_Site)))
April 30, 2014 at 1:11 pm
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 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/
May 1, 2014 at 8:23 am
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
Don't fear failure, fear regret.
May 1, 2014 at 9:29 am
May 1, 2014 at 10:15 am
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/
May 1, 2014 at 10:17 am
Why not try Jeff Moden's splitter, pass space as delimiter?
😎
May 1, 2014 at 10:18 am
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
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 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/
May 1, 2014 at 10:25 am
Eirikur Eiriksson (5/1/2014)
Why not try Jeff Moden's splitter, pass space as delimiter?😎
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 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/
May 1, 2014 at 10:35 am
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
Don't fear failure, fear regret.
May 1, 2014 at 11:54 am
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
May 1, 2014 at 12:23 pm
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/
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply