Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to Seperate number from a text


How to Seperate number from a text

Author
Message
suveshkumar.sb
suveshkumar.sb
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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
bigshopmall
bigshopmall
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 106
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
suveshkumar.sb
suveshkumar.sb
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 7
Thank you for the reply I will check on it and let you know:-)
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5679 Visits: 25280
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
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8468 Visits: 18080
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.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Steven Willis
Steven Willis
SSC-Addicted
SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)

Group: General Forum Members
Points: 473 Visits: 1721
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






 
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8468 Visits: 18080
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.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Steven Willis
Steven Willis
SSC-Addicted
SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)

Group: General Forum Members
Points: 473 Visits: 1721
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





 
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search