Solved

TSQL REPLACE Statement Help

Posted on 2013-05-15
12
445 Views
Last Modified: 2013-05-16
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

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, 

Open in new window


Is there anyway that I can get it done without going through and finding  all the above?
0
Comment
Question by:Tech315
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 39

Expert Comment

by:lcohan
ID: 39169324
Please have a look at link below as I believe it matches exactly what you want to do:

http://stackoverflow.com/questions/106206/fastest-way-to-remove-non-numeric-characters-from-a-varchar-in-sql-server
0
 

Author Comment

by:Tech315
ID: 39169399
I'm severley limted in my tsql writing as u see. How would I throw the above into a patindex ?
0
 
LVL 7

Expert Comment

by:Ross Turner
ID: 39169410
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/

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

Open in new window

0
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
LVL 39

Expert Comment

by:lcohan
ID: 39169534
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(CITIZENS.LNAM)
from CITIZENS q
where CITIZENS.ID = q.ID;
go
0
 
LVL 40

Expert Comment

by:Sharath
ID: 39170577
you can also try like this.
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  

Open in new window


Here is an example: http://sqlfiddle.com/#!3/e4a24/1
0
 

Author Comment

by:Tech315
ID: 39171743
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.
0
 
LVL 40

Expert Comment

by:Sharath
ID: 39171773
Post your table structure and some sample data.
0
 

Author Comment

by:Tech315
ID: 39171811
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
0
 
LVL 40

Expert Comment

by:Sharath
ID: 39172399
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 

Open in new window

see this example: http://sqlfiddle.com/#!3/8c92a/1
0
 

Author Comment

by:Tech315
ID: 39172611
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 ','.


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 

Open in new window

0
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 39172925
You missed the "WITH CTE"
WITH CTE AS (
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   CIVILIAN_2012 t1 
       JOIN CTE1 t2 
         ON t1.IA_LOG_NUMBER = t2.IA_LOG_NUMBER 

Open in new window

0
 

Author Closing Comment

by:Tech315
ID: 39173045
Thanks again Sharath
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

837 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question