June 12, 2013 at 12:45 pm
I've a column name called "Codes" and the datatype is nvarchar. The data looks like
X200, 516, XD1, YTG, 24ZY, 40Y, 01DX
Now, I just need the numeric values from these data. If the value is all letters then I just want to display it as 0.
From the above example, I just want to see it as 200, 516, 1, 0, 24,40, 01
Any help is much appreciated,
Thanks in advance.
June 12, 2013 at 1:28 pm
Maybe a recursive CTE can help you. Note that I'm including the sample data in a CTE and you might not need it when you apply the solution.
WITH Codes(code) AS(
SELECT *
FROM( VALUES('X200'), ('516'), ('XD1'), ('YTG'), ('24ZY'), ('40Y'), ('01DX')) Codes(code)
),
rCTE(code, cleancode, N) AS (
SELECTcode,
CAST( CASE WHEN SUBSTRING( code, 1, 1) NOT LIKE '[^0-9]'
THEN SUBSTRING( code, 1, 1) ELSE '' END AS varchar(10)),
1 AS N
FROM Codes
UNION ALL
SELECTc.code,
CAST(r.cleancode + CASE WHEN SUBSTRING( c.code, N + 1, 1) NOT LIKE '[^0-9]'
THEN SUBSTRING( c.code, N + 1, 1) ELSE '' END AS varchar(10)),
N + 1
FROM Codes c
JOIN rCTE r ON c.code = r.code AND N < LEN( c.code)
)
SELECT code, CAST( cleancode AS int) cleancode
FROM rCTE
WHERE N = LEN( code)
ORDER BY code, N;
I'm not so sure about performance, but if your strings are short, it should be ok.
June 12, 2013 at 2:03 pm
sql1411 (6/12/2013)
I've a column name called "Codes" and the datatype is nvarchar. The data looks likeX200, 516, XD1, YTG, 24ZY, 40Y, 01DX
Now, I just need the numeric values from these data. If the value is all letters then I just want to display it as 0.
From the above example, I just want to see it as 200, 516, 1, 0, 24,40, 01
Any help is much appreciated,
Thanks in advance.
I started with an old thread here on SSC and a post from Jeff Moden doing this in a scalar function. http://www.sqlservercentral.com/Forums/FindPost470579.aspx
I turned this into an iTVF that should be pretty quick.
Here is the function:
create function GetNumbersOnly(@pString varchar(8000))
returns table as return
WITH
E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4)
, ValueList as
(
SELECT N, SUBSTRING(@pString, N, 1) as NewVal
FROM cteTally
WHERE N <= LEN(@pString)
AND SUBSTRING(@pString, N, 1) LIKE ('[0-9]')
)
SELECT top 1 NewVal = replace(STUFF((
SELECT '+' + NewVal
FROM ValueList
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,''), '+', '')
FROM ValueList
Now we can use the excellent sample data that Luis put together.
WITH Codes(code) AS
(
SELECT *
FROM( VALUES('X200'), ('516'), ('XD1'), ('YTG'), ('24ZY'), ('40Y'), ('01DX')) Codes(code)
)
select * from Codes c
cross apply dbo.GetNumbersOnly(c.code) n
--EDIT--
I also used some of the techniques described by Wayne Sheffield in this article. http://www.sqlservercentral.com/articles/comma+separated+list/71700/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 12, 2013 at 2:33 pm
A similar approach to Sean's iTVF.
WITH Codes(code) AS(
SELECT *
FROM( VALUES('X200'), ('516'), ('XD1'), ('YTG'), ('24ZY'), ('40Y'), ('01DX')) Codes(code)
),
E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(Number) AS (
SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)
SELECT code, CAST( ISNULL((SELECT TOP 100 SUBSTRING(a.code, b.Number, 1) AS Val
FROM Codes a, cteTally b
WHERE b.Number <= len(a.code)
AND a.code = c.code
AND SUBSTRING(a.code, b.Number, 1) NOT LIKE '[^0-9]'
ORDER BY a.code, b.Number FOR XML PATH(''),TYPE).value('.','varchar(max)'), '0') AS int) AS cleancode
FROM Codes c
Note for sql1411: Tests are up to you, as well as understanding what is the code doing. If you don't understand it, ASK before implementing it.
June 12, 2013 at 2:55 pm
Thank you v much for helping me out 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply