April 4, 2013 at 5:02 am
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
April 4, 2013 at 7:37 am
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
April 5, 2013 at 9:58 am
Thank you for the reply I will check on it and let you know:-)
April 5, 2013 at 11:33 am
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
April 5, 2013 at 1:09 pm
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]'
April 5, 2013 at 5:40 pm
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
April 8, 2013 at 12:55 pm
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.
April 8, 2013 at 6:50 pm
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply