February 21, 2012 at 2:28 am
I have a requirement here. There is a string '0005 ganeshkumar A999'. I want the last four character 'A999' as output where it is not necessarily a four charactes only. It could be 3 to 6 characters also.
Thanks in advance for your any help coming here, as always. 🙂
Regards,
Ganesh.
February 21, 2012 at 2:44 am
ganeshkumar005 (2/21/2012)
I have a requirement here. There is a string '0005 ganeshkumar A999'. I want the last four character 'A999' as output where it is not necessarily a four charactes only. It could be 3 to 6 characters also.Thanks in advance for your any help coming here, as always. 🙂
Regards,
Ganesh.
I've assumed you always want the characters after the last space (the title suggest after the second space, but what if there are more than two?).
I find it easier to reverse the string and look for the first space instead, and then reverse it back. Others may have a more elegant method, but have a play with this code:
--DECLARE @String VarChar(MAX) = '0005 ganeshkumar A999';
--DECLARE @String VarChar(MAX) = 'first second third fourth';
DECLARE @String VarChar(MAX) = 'first second';
DECLARE @EndString VarChar(MAX);
SET @EndString = LTRIM(REVERSE(LEFT(REVERSE(@String),PATINDEX('% %',@String))));
SELECT @EndString
February 21, 2012 at 3:26 am
another way:
select parsename(replace('0005 ganeshkumar A999',' ','.'),1)
February 21, 2012 at 5:27 am
Thanks to both of you. This works fine.
February 12, 2013 at 2:21 pm
Is there a way to get the first two? For instance I need to pull out 'aabbbb' from 'aa bbbb c ddd ee'. The lengths and the number of spaces will always vary.
February 12, 2013 at 2:38 pm
newbie2 (2/12/2013)
Is there a way to get the first two? For instance I need to pull out 'aabbbb' from 'aa bbbb c ddd ee'. The lengths and the number of spaces will always vary.
Yes look at what Eugene posted. PARSENAME will do this quite nicely.
http://msdn.microsoft.com/en-us/library/ms188006.aspx
_______________________________________________________________
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/
February 12, 2013 at 2:52 pm
I tried that, but it does not seem to work when the amount of spaces vary, for example one column may be '1 north main st' and I need to return '1north', but the next record could be '12 main steet' and I need to return '12main'.
February 12, 2013 at 3:40 pm
newbie2 (2/12/2013)
I tried that, but it does not seem to work when the amount of spaces vary, for example one column may be '1 north main st' and I need to return '1north', but the next record could be '12 main steet' and I need to return '12main'.
The fastest method is probably using DelimitedSplit8K to parse the string, then a PIVOT table to turn the rows into columns so you can do the concatenation. If you don't have the function DelimitedSplit8K just do a search on this site and you'll find it.
--first some sample data
IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
DROP TABLE #TempTable
CREATE TABLE #TempTable (
[ID] INT IDENTITY(1,1) NOT NULL,
[Address] NVARCHAR(50) NULL,
PRIMARY KEY (ID))
INSERT INTO #TempTable
SELECT '1 north main st'
UNION
SELECT '12 main steet'
SELECT
ID
,[1]+[2] AS Street
FROM
(
SELECT
tt.ID
,dsk.ItemNumber
,dsk.Item
FROM
#TempTable AS tt
CROSS APPLY
dbo.DelimitedSplit8K(tt.Address,' ') AS dsk
WHERE
ItemNumber IN (1,2)
) AS src
PIVOT (MAX(Item) FOR ItemNumber IN ([1],[2])) AS pvt
February 12, 2013 at 3:51 pm
Thanks for the quick response, but that's a little more complicated than I was hoping for. I thought maybe using CHARINDEX and SUBSTRING might do it.
February 12, 2013 at 6:27 pm
Perhaps something like this?
WITH MyData ([address]) AS (
SELECT '1 north main st'
UNION ALL SELECT '12 main street'
UNION ALL SELECT '14 clover avenue')
SELECT [address], [address1], [address2]=LEFT([address2], CHARINDEX(' ', [address2])-1)
FROM MyData
-- Remove extra white space
CROSS APPLY (
SELECT REPLACE(
REPLACE(
REPLACE(address, ' ', ' ' + CHAR(7))
,CHAR(7) + ' ', CHAR(7))
,CHAR(7), '')) a([address1])
CROSS APPLY (
SELECT STUFF([address1], CHARINDEX(' ', [address1]), 1, '')) b([address2])
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 12, 2013 at 7:55 pm
Yes, that looks much cleaner. Unfortunately I can't seem to get it to translate to my data:
SELECT MyAddress, MyAddress as [address1], [address2]=LEFT([address2], CHARINDEX(' ', [address2])-1)
FROM MyTable
-- Remove extra white space
CROSS APPLY (SELECT REPLACE(REPLACE(REPLACE(MyAddress, ' ', ' ' + CHAR(7)),CHAR(7) + ' ', CHAR(7)),CHAR(7), '')) a([address1])
CROSS APPLY (SELECT STUFF([address1], CHARINDEX(' ', [address1]), 1, '')) b([address2])
The error is: Msg 537, Level 16, State 2, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.
February 12, 2013 at 8:11 pm
newbie2 (2/12/2013)
Yes, that looks much cleaner. Unfortunately I can't seem to get it to translate to my data:SELECT MyAddress, MyAddress as [address1], [address2]=LEFT([address2], CHARINDEX(' ', [address2])-1)
FROM MyTable
-- Remove extra white space
CROSS APPLY (SELECT REPLACE(REPLACE(REPLACE(MyAddress, ' ', ' ' + CHAR(7)),CHAR(7) + ' ', CHAR(7)),CHAR(7), '')) a([address1])
CROSS APPLY (SELECT STUFF([address1], CHARINDEX(' ', [address1]), 1, '')) b([address2])
The error is: Msg 537, Level 16, State 2, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.
You probably have a string like 'abc abc' where there is no second bit of white space.
You may be able to address it either adding a blank space at the end of the address string or putting a CASE around the second parameter to LEFT (in the first line) that checks for a CHARINDEX result of 0.
With the offending sample data included, I can show you how.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 12, 2013 at 8:26 pm
I was thinking the same thing, but could not get the CHARINDEX to work, so I added the space then trimmed it off. Works fine now.
SELECT MyAddress, address1, address2=RTRIM(LEFT(address2, CHARINDEX(' ', address2 + ' ')-1) )
FROM MyTable
-- Remove extra white space
CROSS APPLY (SELECT REPLACE(REPLACE(REPLACE(MyAddress, ' ', ' ' + CHAR(7)),CHAR(7) + ' ', CHAR(7)),CHAR(7), '')) a(address1)
CROSS APPLY (SELECT STUFF(address1, CHARINDEX(' ', address1), 1, '')) b(address2)
Thanks so much for your help, and the quick response! BTW, nice fish in the picture 🙂
February 12, 2013 at 8:30 pm
Glad you liked the response and the fish! 😀
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply