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

How to Seperate number from a text Expand / Collapse
Author
Message
Posted Thursday, April 4, 2013 5:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 3:56 AM
Points: 2, Visits: 7
Hi,

Here is my scenario, i have set a data in my column

SBA 60 OFF 19.99 NOW 7.99
SBA 50 OFF 99.99 NOW 49.99
ST. PATRICK 19.99
QC CUSTOM FLORAL
45.23 ST. PATRICK
QC CUSTOM FLORAL $ 43.34

Now I want to fetch the number at the end and at the beginning
like this

7.99
49.99
19.99
45.23
43.34

Please help me in solving this case.

Thank's in Advance
Post #1438715
Posted Thursday, April 4, 2013 7:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 4:55 AM
Points: 1, Visits: 94
Suppose the column name of table is mValue and tablename is xy then following query will give the result:

select mvalue,RequiredValue = case when ISNUMERIC(left(mvalue,1))=1 then SUBSTRING(mvalue,1,CHARINDEX(' ',mvalue)-1)
when ISNUMERIC(right(mvalue,1))=1 then right(mvalue,CHARINDEX(' ',reverse(mvalue))-1)
else '' end from xy
Post #1438802
Posted Friday, April 5, 2013 9:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 3:56 AM
Points: 2, Visits: 7
Thank you for the reply I will check on it and let you know
Post #1439327
Posted Friday, April 5, 2013 11:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:17 PM
Points: 5,332, Visits: 25,261
bigshopmall (4/4/2013)
Suppose the column name of table is mValue and tablename is xy then following query will give the result:

select mvalue,RequiredValue = case when ISNUMERIC(left(mvalue,1))=1 then SUBSTRING(mvalue,1,CHARINDEX(' ',mvalue)-1)
when ISNUMERIC(right(mvalue,1))=1 then right(mvalue,CHARINDEX(' ',reverse(mvalue))-1)
else '' end from xy


Be very careful of using ISNUMERIC .... read this article by Jeff Moden to learn the problems possible with using ISNUMERIC

http://www.sqlservercentral.com/articles/IsNumeric/71512/


If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #1439385
Posted Friday, April 5, 2013 1:09 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 10:43 AM
Points: 3,615, Visits: 8,112
This should do the trick to avoid ISNUMERIC

WITH CTE AS(
SELECT 'SBA 60 OFF 19.99 NOW 7.99' as String
UNION ALL
SELECT 'SBA 50 OFF 99.99 NOW 49.99'
UNION ALL
SELECT 'ST. PATRICK 19.99'
UNION ALL
SELECT 'QC CUSTOM FLORAL'
UNION ALL
SELECT '45.23 ST. PATRICK'
UNION ALL
SELECT 'QC CUSTOM FLORAL $ 43.34')
SELECT CASE WHEN String LIKE '[0-9]%' THEN LEFT( String, CHARINDEX( ' ', String) -1)
WHEN String LIKE '%[0-9]' THEN RIGHT( String, CHARINDEX( ' ', REVERSE(String))-1)
ELSE '' END
FROM CTE
--WHERE String LIKE '[0-9]%' --Filters to avoid empty values.
--OR String LIKE '%[0-9]'




Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1439441
Posted Friday, April 5, 2013 5:40 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
I don't know if this is any better than Luis' code above, but it gives you another alternative way of looking at the problem.


;WITH sampledata AS
(
SELECT * FROM
(VALUES
('SBA 60 OFF 19.99 NOW 7.99'),
('SBA 50 OFF 99.99 NOW 49.99'),
('ST. PATRICK 19.99'),
('QC CUSTOM FLORAL'),
('45.23 ST. PATRICK'),
('QC CUSTOM FLORAL $ 43.34')
) DATA (PriceText))
,
cteElements AS --split the strings and order the rows from right to left
(
SELECT
ROW_NUMBER() OVER (ORDER BY PriceText) AS ID
,ROW_NUMBER() OVER (PARTITION BY ItemNumber ORDER BY ItemNumber) AS RowNum
,ItemNumber
,REVERSE(Item) AS Element
,s.PriceText
FROM
sampledata s
CROSS APPLY
dbo.DelimitedSplit8K(REVERSE(s.PriceText),' ') AS dsk
)
,
ctePrices AS --find elements that appear to be a price
(
SELECT
ID
,RowNum
,ItemNumber
,Element
,N
,PriceText
FROM
cteElements e
CROSS APPLY
dbo.Tally t
-- these filters may need to altered to fit the data
WHERE
N <= e.ItemNumber
AND CHARINDEX('.',e.Element) > 0
AND ISNUMERIC(e.Element) = 1
--yes, ISNUMERIC is flawed. But it might be OK here.
--I'd consider using a validation function.
)
,
cteRanking AS --put the elements in some useful order
(
SELECT
ROW_NUMBER() OVER (PARTITION BY PriceText ORDER BY ID) AS RowNum
,ROW_NUMBER() OVER (PARTITION BY PriceText,Element ORDER BY ID) AS ItemNum
,Element
,PriceText
,ID
,RANK() OVER (PARTITION BY PriceText ORDER BY ID) AS [Rank]
FROM
ctePrices
)
SELECT DISTINCT
cte.Element
,s.PriceText
FROM
cteRanking cte
RIGHT OUTER JOIN --this join is to bring back in any text that has no price
sampledata s
ON cte.PriceText = s.PriceText
WHERE
RowNum = 1
OR RowNum IS NULL




 
Post #1439533
Posted Monday, April 8, 2013 12:55 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 10:43 AM
Points: 3,615, Visits: 8,112
Steven, it's great that you look for alternate solutions. However, you might want to make it simpler. You're scaning the "table" multiple times and using several window functions. If you're looking for something more complete, you can tweak you're code to find prices in any place of the strings.
Could you explain what are you using the RANK function? I know what it does, but it seems to me that you're not using it.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1439986
Posted Monday, April 8, 2013 6:50 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
I arranged the solution in a verbose manner so that the original poster could follow the steps I used to parse the data. The problem with that data is that the position of the desired price is almost random. Based on the OP, I had to make an assumption about the business rules involved with this data. That assumption is that the first decimal from the RIGHT was the value he wanted to show as the price. But sometimes there is more than one decimal in the string, sometimes none, or sometimes at the first position. Also, his data did not have a primary key so I created one for my own use with one of the first ROW_NUMBER invocations.

I'm posting a revised version of the script below. To make the script more compact, I went ahead and added a PK to the data so I would have that to work with and joined as many rows as possible with a single query. I've used both a WITH form of query and a query with subquery that accomplish the same thing in this case.

As for the RANK function. Since some of the rows had more than one "candidate" price, I used RANK to put them in the correct order with the value closest to the right being ranked higher than one to the left of it.


;WITH sampledata AS
(
SELECT * FROM
(VALUES
(1,'SBA 60 OFF 19.99 NOW 7.99'),
(2,'SBA 50 OFF 99.99 NOW 49.99'),
(3,'ST. PATRICK 19.99'),
(4,'QC CUSTOM FLORAL'),
(5,'45.23 ST. PATRICK'),
(6,'QC CUSTOM FLORAL $ 43.34')
) DATA (ID,PriceText))
,
ctePrices AS
(
SELECT DISTINCT
ID
,ItemNumber
,REVERSE(Item) AS Element
,s.PriceText
,RANK() OVER (PARTITION BY PriceText ORDER BY ItemNumber) AS [Rank]
FROM
sampledata s
CROSS APPLY
dbo.DelimitedSplit8K(REVERSE(s.PriceText),' ') AS dsk
CROSS APPLY
dbo.Tally t
WHERE
N <= dsk.ItemNumber
AND CHARINDEX('.',REVERSE(dsk.Item)) > 0
AND ISNUMERIC(REVERSE(dsk.Item)) = 1
)
SELECT
s.ID
,s.PriceText
,cte.Element
FROM
ctePrices cte
RIGHT OUTER JOIN
sampledata s
ON cte.PriceText = s.PriceText
WHERE
cte.[Rank] = 1
OR cte.[Rank] IS NULL
ORDER BY
s.ID



;WITH sampledata AS
(
SELECT * FROM
(VALUES
(1,'SBA 60 OFF 19.99 NOW 7.99'),
(2,'SBA 50 OFF 99.99 NOW 49.99'),
(3,'ST. PATRICK 19.99'),
(4,'QC CUSTOM FLORAL'),
(5,'45.23 ST. PATRICK'),
(6,'QC CUSTOM FLORAL $ 43.34')
) DATA (ID,PriceText))
SELECT
s.ID
,s.PriceText
,cte.Element
FROM
(
SELECT DISTINCT
ID
,ItemNumber
,REVERSE(Item) AS Element
,s.PriceText
,RANK() OVER (PARTITION BY PriceText ORDER BY ItemNumber) AS [Rank]
FROM
sampledata s
CROSS APPLY
dbo.DelimitedSplit8K(REVERSE(s.PriceText),' ') AS dsk
CROSS APPLY
dbo.Tally t
WHERE
N <= dsk.ItemNumber
AND CHARINDEX('.',REVERSE(dsk.Item)) > 0
AND ISNUMERIC(REVERSE(dsk.Item)) = 1
) cte
RIGHT OUTER JOIN
sampledata s
ON cte.PriceText = s.PriceText
WHERE
cte.[Rank] = 1
OR cte.[Rank] IS NULL
ORDER BY
s.ID



 
Post #1440089
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse