## How to Seperate number from a text

 Author Message suveshkumar.sb Forum Newbie Group: General Forum Members Points: 2 Visits: 7 Hi,Here is my scenario, i have set a data in my columnSBA 60 OFF 19.99 NOW 7.99SBA 50 OFF 99.99 NOW 49.99ST. PATRICK 19.99QC CUSTOM FLORAL45.23 ST. PATRICKQC CUSTOM FLORAL \$ 43.34Now I want to fetch the number at the end and at the beginninglike this7.9949.9919.9945.2343.34Please help me in solving this case.Thank's in Advance bigshopmall Forum Newbie 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 Forum Newbie Group: General Forum Members Points: 2 Visits: 7 Thank you for the reply I will check on it and let you know:-) bitbucket-25253 SSC-Dedicated Group: General Forum Members Points: 34619 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 xyBe very careful of using ISNUMERIC .... read this article by Jeff Moden to learn the problems possible with using ISNUMERIChttp://www.sqlservercentral.com/articles/IsNumeric/71512/ If everything seems to be going well, you have obviously overlooked something. RonPlease help us, help you -before posting a question please read Before posting a performance problem please read Luis Cazares SSC Guru Group: General Forum Members Points: 95001 Visits: 21186 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 '' ENDFROM 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 SSCertifiable Group: General Forum Members Points: 5257 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.PriceTextFROM cteRanking cteRIGHT OUTER JOIN --this join is to bring back in any text that has no price sampledata s ON cte.PriceText = s.PriceTextWHERE RowNum = 1 OR RowNum IS NULL` Luis Cazares SSC Guru Group: General Forum Members Points: 95001 Visits: 21186 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 SSCertifiable Group: General Forum Members Points: 5257 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.ElementFROM ctePrices cteRIGHT OUTER JOIN sampledata s ON cte.PriceText = s.PriceText WHERE cte.[Rank] = 1 OR cte.[Rank] IS NULLORDER 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.ElementFROM ( 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 ) cteRIGHT OUTER JOIN sampledata s ON cte.PriceText = s.PriceText WHERE cte.[Rank] = 1 OR cte.[Rank] IS NULLORDER BY s.ID`