Tech315
asked on
TSQL REPLACE Statement Help
Hi to all experts,
I have a replace statement that seems to be growing out of control. I wanted to replace everything but numeric characters in my column
Is there anyway that I can get it done without going through and finding all the above?
I have a replace statement that seems to be growing out of control. I wanted to replace everything but numeric characters in my column
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE
(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(varchar(85),dbo.CITIZENS.LNAM),'/',' '),'#',' '),'\',' ')
,'ANONYMOUS',''),'Rubin aka A S',''),'A Source',''),'Anon',''),'CS',''),'C/I',''),'DEA C/I',''),'MOS A/C',''),'A','')
,'C',''),'FEMALE',''),'ASCB',''),')',''),'(',''),' (Koren)',''),'V','') AS AI_NUMBER,
Is there anyway that I can get it done without going through and finding all the above?
ASKER
I'm severley limted in my tsql writing as u see. How would I throw the above into a patindex ?
I would use this to build the function and then do your select
Example here:
http://blog.sqlauthority.com/2008/10/14/sql-server-get-numeric-value-from-alpha-numeric-string-udf-for-get-numeric-numbers-only/
Example here:
http://blog.sqlauthority.com/2008/10/14/sql-server-get-numeric-value-from-alpha-numeric-string-udf-for-get-numeric-numbers-only/
CREATE FUNCTION dbo.udf_GetNumeric
(@strAlphaNumeric VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @intAlpha INT
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)
BEGIN
WHILE @intAlpha > 0
BEGIN
SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )
END
END
RETURN ISNULL(@strAlphaNumeric,0)
END
GO
Then you could use the function in a statement like below with 2 assumptions:
1. MUST backup your table data before running the update like
SELECT * INTO dbo.CITIZENS_BACKUP FROM dbo.CITIZENS;
GO
2. MUST check the data type for CITIZENS.LNAM and if it is NOT like VARCHAR(256) then you must adapt the code function posted above to match your CITIZENS.LNAM data type.
update dbo.CITIZENS set LNAM = dbo.udf_GetNumeric(CITIZEN S.LNAM)
from CITIZENS q
where CITIZENS.ID = q.ID;
go
1. MUST backup your table data before running the update like
SELECT * INTO dbo.CITIZENS_BACKUP FROM dbo.CITIZENS;
GO
2. MUST check the data type for CITIZENS.LNAM and if it is NOT like VARCHAR(256) then you must adapt the code function posted above to match your CITIZENS.LNAM data type.
update dbo.CITIZENS set LNAM = dbo.udf_GetNumeric(CITIZEN
from CITIZENS q
where CITIZENS.ID = q.ID;
go
you can also try like this.
Here is an example: http://sqlfiddle.com/#!3/e4a24/1
WITH CTE
AS (SELECT *,
SUBSTRING(AlphaNumericData, N, 1) Sub_AlphaNumericData
FROM tblAlphanumeric t1
JOIN (SELECT NUMBER
FROM MASTER..spt_values
WHERE TYPE = 'p') Numbers(N)
ON N BETWEEN 1 AND LEN(AlphaNumericData)),
CTE_1
AS (SELECT *,
CASE
WHEN Sub_AlphaNumericData LIKE '[0-9]' THEN Sub_AlphaNumericData
ELSE ''
END NUMBER
FROM CTE),
CTE1
AS (SELECT DISTINCT id,
AlphaNumericData,
ISNULL(STUFF((SELECT CONVERT(VARCHAR(5), NUMBER)
FROM CTE_1 t1
WHERE t1.id = t2.id
AND t1.NUMBER <> ''
FOR XML PATH('')), 1, 0, ''), '') Numbers
FROM CTE_1 t2)
UPDATE t1
SET t1.AlphaNumericData = t2.Numbers
FROM tblAlphanumeric t1
JOIN CTE1 t2
ON t1.ID = t2.ID
Here is an example: http://sqlfiddle.com/#!3/e4a24/1
ASKER
Sharath I'm sorry seems complex, how do I go about using the above with my column? Sorry I know I'm asking to be spoon fed. I'm no sql guy.
Post your table structure and some sample data.
ASKER
Current out put: I have about 66K like below where CID number has alpha/numeric
I'm just looking to keep the Numeric part and discard the rest. The letters vary in the CID_NUMBER can b3 3 characters can be 12 characters.
Current:
ORDER_NUMBER ORDER_YEAR LAST_NAME FIRST_NAME CID_NUMBER
123 2012 DIMI SAL AS-12-2713
124 2013 MIMI PAT CI-12-2813
125 2013 GIMI STEVE OC-12-2615
126 2013 SIMI PETE MAS-126-2555
127 2013 RIMI Jorge ADA-12809
What I need:
ORDER_NUMBER ORDER_YEAR LAST_NAME FIRST_NAME CID_NUMBER
123 2012 DIMI SAL 12-2713
124 2013 MIMI PAT 12-2813
125 2013 GIMI STEVE 12-2615
126 2013 SIMI PETE 126-2555
127 2013 RIMI Jorge 12809
Thxs
I'm just looking to keep the Numeric part and discard the rest. The letters vary in the CID_NUMBER can b3 3 characters can be 12 characters.
Current:
ORDER_NUMBER ORDER_YEAR LAST_NAME FIRST_NAME CID_NUMBER
123 2012 DIMI SAL AS-12-2713
124 2013 MIMI PAT CI-12-2813
125 2013 GIMI STEVE OC-12-2615
126 2013 SIMI PETE MAS-126-2555
127 2013 RIMI Jorge ADA-12809
What I need:
ORDER_NUMBER ORDER_YEAR LAST_NAME FIRST_NAME CID_NUMBER
123 2012 DIMI SAL 12-2713
124 2013 MIMI PAT 12-2813
125 2013 GIMI STEVE 12-2615
126 2013 SIMI PETE 126-2555
127 2013 RIMI Jorge 12809
Thxs
try this script. replace test with your actual table name.
;WITH CTE
AS (SELECT *,
SUBSTRING(CID_NUMBER, N, 1) Sub_CID_NUMBER
FROM test t1
JOIN (SELECT NUMBER
FROM MASTER..spt_values
WHERE TYPE = 'p') Numbers(N)
ON N BETWEEN 1 AND LEN(CID_NUMBER)),
CTE_1
AS (SELECT *,
CASE
WHEN Sub_CID_NUMBER LIKE '[0-9]'
OR Sub_CID_NUMBER = '-' THEN Sub_CID_NUMBER
ELSE ''
END NUMBER
FROM CTE),
CTE1
AS (SELECT DISTINCT ORDER_NUMBER,
CID_NUMBER,
ISNULL(STUFF((SELECT CONVERT(VARCHAR(5), NUMBER)
FROM CTE_1 t1
WHERE t1.ORDER_NUMBER = t2.ORDER_NUMBER
AND t1.NUMBER <> ''
FOR XML PATH('')), 1, 0, ''), '') Numbers
FROM CTE_1 t2)
UPDATE t1
SET t1.CID_NUMBER = CASE
WHEN LEFT(t2.Numbers, 1) = '-' THEN SUBSTRING(t2.Numbers, 2, LEN(t2.Numbers))
ELSE t2.Numbers
END
FROM test t1
JOIN CTE1 t2
ON t1.ORDER_NUMBER = t2.ORDER_NUMBER
see this example: http://sqlfiddle.com/#!3/8c92a/1
ASKER
Ok Sharath almost there getting the following error:
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 15
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 15
Incorrect syntax near ','.
SELECT *,
SUBSTRING(CI_NUMBER, N, 1) Sub_CI_NUMBER
FROM CIVILIAN_2012
JOIN (SELECT NUMBER
FROM MASTER..spt_values
WHERE TYPE = 'p') Numbers(N)
ON N BETWEEN 1 AND LEN(CI_NUMBER)),
CTE_1
AS (SELECT *,
CASE
WHEN Sub_CI_NUMBER LIKE '[0-9]'
OR Sub_CI_NUMBER = '-' THEN Sub_CI_NUMBER
ELSE ''
END NUMBER
FROM CTE),
CTE1
AS (SELECT DISTINCT IA_LOG_NUMBER,
CI_NUMBER,
ISNULL(STUFF((SELECT CONVERT(VARCHAR(5), NUMBER)
FROM CTE_1 t1
WHERE t1.IA_LOG_NUMBER = t2.IA_LOG_NUMBER
AND t1.NUMBER <> ''
FOR XML PATH('')), 1, 0, ''), '') Numbers
FROM CTE_1 t2)
UPDATE t1
SET t1.CI_NUMBER = CASE
WHEN LEFT(t2.Numbers, 1) = '-' THEN SUBSTRING(t2.Numbers, 2, LEN(t2.Numbers))
ELSE t2.Numbers
END
FROM test t1
JOIN CTE1 t2
ON t1.IA_LOG_NUMBER = t2.IA_LOG_NUMBER
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks again Sharath
http://stackoverflow.com/questions/106206/fastest-way-to-remove-non-numeric-characters-from-a-varchar-in-sql-server