Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Get 1st Word in the string Expand / Collapse
Author
Message
Posted Wednesday, April 30, 2014 12:48 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 9:09 AM
Points: 215, Visits: 638
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 .
Post #1566555
Posted Wednesday, April 30, 2014 12:53 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 9:09 AM
Points: 215, Visits: 638
No Worries, Found a Link that helped.

LTRIM(LEFT(p.Delivery_Site, CHARINDEX(' ',p.Delivery_Site)))
Post #1566558
Posted Wednesday, April 30, 2014 1:11 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:36 PM
Points: 13,111, Visits: 11,946
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)
Post #1566571
Posted Thursday, May 1, 2014 8:23 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:51 PM
Points: 276, Visits: 1,003
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.


  Post Attachments 
SQLQuery1.txt (7 views, 1.20 KB)
Post #1566728
Posted Thursday, May 1, 2014 9:29 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 1:31 PM
Points: 280, Visits: 615
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]
Post #1566754
Posted Thursday, May 1, 2014 10:15 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:36 PM
Points: 13,111, Visits: 11,946
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)
Post #1566766
Posted Thursday, May 1, 2014 10:17 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 11:01 PM
Points: 1,307, Visits: 3,746
Why not try Jeff Moden's splitter, pass space as delimiter?
Post #1566767
Posted Thursday, May 1, 2014 10:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:36 PM
Points: 13,111, Visits: 11,946
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)
Post #1566769
Posted Thursday, May 1, 2014 10:25 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:36 PM
Points: 13,111, Visits: 11,946
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 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)
Post #1566770
Posted Thursday, May 1, 2014 10:34 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:51 PM
Points: 276, Visits: 1,003
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
Post #1566776
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse