September 19, 2013 at 6:10 am
Hi,
I would like to know how to retrieve integer,decimals from string in table format.
Input string: < 2% annual < 0.16% monthly
Input string: < 5% annual < 0.41% monthly
Input string: > 3% annual > 0.25% monthly
Input string: > 3% annual > 25% monthly
Expected output:
Num1 Num2
-------------
2 0.16
5 0.41
3 0.25
3 25
Create table script:
create table tbl_RetrieveNumbers( Samplestring varchar(100))
insert into tbl_RetrieveNumbers values('< 2% annual < 0.16% monthly')
insert into tbl_RetrieveNumbers values('< 5% annual < 0.41% monthly')
insert into tbl_RetrieveNumbers values('> 3% annual > 0.25% monthly')
insert into tbl_RetrieveNumbers values('> 3% annual > 25% monthly')
Thanks,
Kumar.
September 19, 2013 at 6:43 am
create function returnnumerics(@inputword varchar(256))
returns varchar(256)
as
begin
declare @indexpositon int
set @indexpositon=PATINDEX('%[^0-9.]%',@inputword)
while @indexpositon>0
begin
set @inputword=STUFF(@inputword,@indexpositon,1,'')
set @indexpositon=PATINDEX('%[^0-9.]%',@inputword)
end
return isnull(@inputword,0)
end
try this query let me it works for you
September 19, 2013 at 7:00 am
Hi chris,
Thanks for sharing the function.
I got below output from your code: ( two numbers in a single column)
20.16
50.41
30.25
325
Expected output: ( two numbers in a two separate columns)
Num1 Num2
-------------
2 0.16
5 0.41
3 0.25
3 25
September 19, 2013 at 8:55 am
No need for a loop. Here's another option that will work with 2 values per line. For more values, you would need to do some changes.
For the code of dbo.DelimitedSplit8K check the following article http://www.sqlservercentral.com/articles/Tally+Table/72993/
create table #tbl_RetrieveNumbers( Samplestring varchar(100))
insert into #tbl_RetrieveNumbers values
('< 2% annual < 0.16% monthly'),
('< 5% annual < 0.41% monthly'),
('> 3% annual > 0.25% monthly'),
('> 3% annual > 25% monthly');
WITH CTE AS(
SELECT Samplestring,
CAST( CASE WHEN ItemNumber = MIN( ItemNumber) OVER( PARTITION BY SampleString)
THEN LEFT( Item, LEN( Item) - 1) END AS decimal( 10,2)) AS Num1,
CAST( CASE WHEN ItemNumber = MAX( ItemNumber) OVER( PARTITION BY SampleString)
THEN LEFT( Item, LEN( Item) - 1) END AS decimal( 10,2)) AS Num2
FROM #tbl_RetrieveNumbers
CROSS APPLY dbo.DelimitedSplit8K( Samplestring, ' ')
WHERE Item LIKE '[0-9]%'
)
SELECT MAX( Num1), MAX( Num2)
FROM CTE
GROUP BY Samplestring
DROP table #tbl_RetrieveNumbers
September 19, 2013 at 7:15 pm
PatternSplitCM (see the 4th article in my signature links) is a high performance tool to facilitate tasks such as this one. Makes the code look pretty simple too.
WITH SampleData (MyStr) AS (
SELECT MyStr
FROM (
VALUES
('< 2% annual < 0.16% monthly'),
('< 5% annual < 0.41% monthly'),
('> 3% annual > 0.25% monthly'),
('> 3% annual > 25% monthly')) a(MyStr))
SELECT MyStr
,Pct=MAX(CASE WHEN ItemNumber=2 THEN Item END)
,Rate=MAX(CASE WHEN ItemNumber=4 THEN Item END)
FROM SampleData
CROSS APPLY dbo.PatternSplitCM(MyStr, '[0-9.]')
WHERE Matched = 1
GROUP BY MyStr;
Give it a try.
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
September 19, 2013 at 11:07 pm
Thanks a lot to both Luis and Dwain.
I am able to get the expected output.
Regards,
Kumar.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply