February 6, 2013 at 12:02 pm
Hi,
DECLARE @NUMERO VARCHAR(15)
SET @NUMERO='00-908/88AB***;'
SET @NUMERO=REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@NUMERO,'-',''),'/',''),'A',''),'B',''),'*',''),';','')
SELECT @NUMERO
RESULT - 0090888
Is there a way to do this in a single command, eg using PATINDEX?
THANKS,
Carlos
February 6, 2013 at 12:28 pm
here's just one of many ways to do it: in this case with a scalar function:
SELECT dbo.StripNonNumeric(@NUMERO)
CREATE FUNCTION StripNonNumeric(@OriginalText VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
DECLARE @CleanedText VARCHAR(8000)
;WITH tally (N) as
(SELECT TOP 10000 row_number() OVER (ORDER BY sc1.id)
FROM Master.dbo.SysColumns sc1
CROSS JOIN Master.dbo.SysColumns sc2)
SELECT @CleanedText = ISNULL(@CleanedText,'') +
CASE
--ascii numbers are 48(for '0') thru 57 (for '9')
WHEN ASCII(SUBSTRING(@OriginalText,Tally.N,1)) BETWEEN 48 AND 57 THEN SUBSTRING(@OriginalText,Tally.N,1) ELSE '' END
FROM tally WHERE Tally.N <= LEN(@OriginalText)
RETURN @CleanedText
END
Lowell
February 6, 2013 at 12:33 pm
FANTASTIC :w00t: ...
responding very quickly ..
THANK YOU
February 7, 2013 at 3:12 am
Good Morning,
to 200,000 lines is very, very slow ..... :crying:
to 1 line, it's good ...
I will continue using the replace ...
It is for a data conversion will not be used constantly!!
Thank you.
February 7, 2013 at 3:51 am
carlosaamaral (2/7/2013)
Good Morning,to 200,000 lines is very, very slow ..... :crying:
to 1 line, it's good ...
I will continue using the replace ...
It is for a data conversion will not be used constantly!!
Thank you.
Here's an alternative:
-- Function:
-- PatternSplitCM will split a string based on a pattern of the form
-- supported by LIKE and PATINDEX
--
-- Created by: Chris Morris/Dwain Camps 12-Oct-2012
CREATE FUNCTION [dbo].[PatternSplitCM]
(
@List VARCHAR(8000) = NULL
,@Pattern VARCHAR(50)
) RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
WITH numbers AS (
SELECT TOP(ISNULL(DATALENGTH(@List), 0))
n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) f (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) g (n))
SELECT
ItemNumber = ROW_NUMBER() OVER(ORDER BY MIN(n)),
Item = SUBSTRING(@List,MIN(n),1+MAX(n)-MIN(n)),
[Matched]
FROM (
SELECT n, y.[Matched], Grouper = n - ROW_NUMBER() OVER(ORDER BY y.[Matched],n)
FROM numbers
CROSS APPLY (
SELECT [Matched] = CASE WHEN SUBSTRING(@List,n,1) LIKE @Pattern THEN 1 ELSE 0 END
) y
) d
GROUP BY [Matched], Grouper
GO
-------------------------------------------------------------------------------------------------
-- Usage:
;WITH Strings AS (
SELECT String = 'AJKY.GSEFD6,7345535HNB,BBN4343.434'
UNION ALL SELECT '00-908/88AB***;'
)
SELECT
s.String,
NewString = STUFF((SELECT '' + Item
FROM dbo.PatternSplitCM (s.String,'[.0-9]')
WHERE Matched = 1
ORDER BY ItemNumber
FOR XML PATH('')), 1, 1, '')
FROM Strings s
-- or:
;WITH Strings AS (
SELECT String = 'AJKY.GSEFD6,7345535HNB,BBN4343.434'
UNION ALL SELECT '00-908/88AB***;'
)
SELECT
s.String,
x.NewString
FROM Strings s
CROSS APPLY (
SELECT NewString = STUFF((SELECT '' + Item
FROM dbo.PatternSplitCM (s.String,'[.0-9]')
WHERE Matched = 1
ORDER BY ItemNumber
FOR XML PATH('')), 1, 1, '')
) x
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 7, 2013 at 6:04 am
Hello Chris,
Sorry - I may be doing something wrong ...
Message 102, Level 15, State 1, Line 11
Incorrect syntax near '.'.
;WITH Strings AS (
SELECT String = 'AJKY.GSEFD6,7345535HNB,BBN4343.434'
UNION ALL SELECT '00-908/88AB***;'
)
SELECT
s.String,
x.NewString
FROM Strings s
CROSS APPLY (
SELECT NewString = STUFF((SELECT '' + Item
FROM dbo.PatternSplitCM (s.String,'[0-9]') -- Suggested error here
WHERE Matched = 1
ORDER BY ItemNumber
FOR XML PATH('')), 1, 1, '')
) x
-- SQLSERVER 2008 R2
how I use them in a update ...?
update table set column = ....
Thank you.
February 7, 2013 at 6:16 am
that syntax error:
Msg 102, Level 15, State 1, Line 13
Incorrect syntax near '.'.
shows that your database is currently at Compatibility 80,and needs to be 90 or above, i think:
ALTER DATABASE [Dictionary] SET COMPATIBILITY_LEVEL = 100
Lowell
February 7, 2013 at 6:28 am
carlosaamaral (2/7/2013)
Hello Chris,Sorry - I may be doing something wrong ...
Message 102, Level 15, State 1, Line 11
Incorrect syntax near '.'.
;WITH Strings AS (
SELECT String = 'AJKY.GSEFD6,7345535HNB,BBN4343.434'
UNION ALL SELECT '00-908/88AB***;'
)
SELECT
s.String,
x.NewString
FROM Strings s
CROSS APPLY (
SELECT NewString = STUFF((SELECT '' + Item
FROM dbo.PatternSplitCM (s.String,'[0-9]') -- Suggested error here
WHERE Matched = 1
ORDER BY ItemNumber
FOR XML PATH('')), 1, 1, '')
) x
-- SQLSERVER 2008 R2
how I use them in a update ...?
update table set column = ....
Thank you.
Are you sure this is the version of the instance you're connected to, and not the version of the client (SSMS)?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 7, 2013 at 7:32 am
OPS... living and learning ....
changed to level 100 - perfect ..
; WITH THE Strings (
SELECT String = 'AJKY.GSEFD6, 7345535HNB, BBN4343.434'
UNION ALL SELECT '11-908 / *** 88AB; '
)
SELECT
s.String,
x.NewString
FROM Strings s
CROSS APPLY (
NewString = SELECT STUFF ((SELECT'' + Item
FROM dbo.PatternSplitCM (s.String, '[0-9]')
WHERE Matched = 1
ORDER BY ItemNumber
FOR XML PATH ('')), 1, 1,'')
) x
I noticed that the first number is always ignored, eg:
'AJKY.GSEFD6, 7345535HNB, BBN4343.434'
returned, 73455354343434 ... The initial 6 was skipped ... (AJKY.GSEFD6, 734 ....)
Guys, thank you very much ... I used the REPLACE .. It was a Firebird database, converted to SQLSERVER ..
February 7, 2013 at 7:51 am
carlosaamaral (2/7/2013)
OPS... living and learning ....changed to level 100 - perfect ..
; WITH THE Strings (
SELECT String = 'AJKY.GSEFD6, 7345535HNB, BBN4343.434'
UNION ALL SELECT '11-908 / *** 88AB; '
)
SELECT
s.String,
x.NewString
FROM Strings s
CROSS APPLY (
NewString = SELECT STUFF ((SELECT'' + Item
FROM dbo.PatternSplitCM (s.String, '[0-9]')
WHERE Matched = 1
ORDER BY ItemNumber
FOR XML PATH ('')), 1, 1,'')
) x
I noticed that the first number is always ignored, eg:
'AJKY.GSEFD6, 7345535HNB, BBN4343.434'
returned, 73455354343434 ... The initial 6 was skipped ... (AJKY.GSEFD6, 734 ....)
Guys, thank you very much ... I used the REPLACE .. It was a Firebird database, converted to SQLSERVER ..
My mistake, sorry - don't need the STUFF as there's no leading comma...
;WITH Strings AS (
SELECT String = 'AJKY.GSEFD6,7345535HNB,BBN4343.434'
UNION ALL SELECT '00-908/88AB***;'
)
SELECT
s.String,
x.*
FROM Strings s
CROSS APPLY (
SELECT NewString = (
SELECT '' + item
FROM dbo.PatternSplitCM (s.String,'[0-9]') -- Suggested error here
WHERE Matched = 1
ORDER BY ItemNumber
FOR XML PATH(''))
) x
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 7, 2013 at 9:12 am
Thank you....
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply