Removing characters from a alphanumeric field SQL

Question

Im moving data from one table to another using insert into. in the select bit need to transfer from column with characters and numerical in to another with only the numerical. The original column is in varchar format.

original column -
ABC100 XYZ:200 DD2000

Wanted column 100 200 2000

Cant write a function because cant have a function in side select statement when inserting

Using MS SQL

Answer 1

You can probably do that with a regex replace. The syntax for this depends on your database software (which you haven't specified).

You should be able to do function calls in your SELECT statement, even when you're using it to INSERT INTO.

Answer 2

If your data is fixed-format I'd do something like

INSERT INTO SOME_TABLE(COLUMN1, COLUMN2, COLUMN3)
  SELECT TO_NUMBER(SUBSTR(SOURCE_COLUMN, 4, 3)),
         TO_NUMBER(SUBSTR(SOURCE_COLUMN, 12, 3)),
         TO_NUMBER(SUBSTR(SOURCE_COLUMN, 18, 4))
    FROM SOME_OTHER_TABLE
    WHERE <conditions>;

The above code is for Oracle. Depending on the database you're using you may have to do things a bit differently.

I hope this helps.

Answer 3

You can always use the Replace TSQL nested. I know its ugly but some times its the only way to get the job done when cleaning up tables in a migration. There are only 26 characters you need to worry about + the number of special characters you have (IE :!@#$%^&* etc...). Yes you will need to data-mine your data to know all the exceptions but this will get you to your goal.

EX.

INSERT INTO [TableName]
SET [Value] = 
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE( UPPER([LegacyValue]) , 'A', '') 
, 'B', '')
, 'C', '')
, 'X', '')
, 'Y', '')
, 'Z', '')
, 'D', '')
, ':', '')
FROM [LegacyTable] 

Answer 4

I encourage you to read this:

Extracting Data

There is an example function that removes alpha characters from a string. This will be much faster than a bunch of replace statements.

Answer 5

You certainly can have a function inside a SELECT statement during an INSERT:

INSERT INTO CleanTable (CleanColumn)
SELECT dbo.udf_CleanString(DirtyColumn)
FROM DirtyTable

Your main problem is going to be getting the function right (the one the G Mastros linked to is pretty good) and getting it performing. If you're only talking thousands of rows, this should be fine. If you are talking about millions of rows, you might need a different strategy.

Answer 6

Writing a UDF is how I've solved this problem in the past. However, I got to thinking if there was a set-based solution. Here's what I have:

First my table which I used Red Gate's Data Generator to populate with a bunch of random alpha numeric values:

Create Table MixedValues    ( 
                            Id int not null identity(1,1) Primary Key
                            , AlphaValue varchar(50)
                            )

Next I built a Tally table on the fly using a CTE but normally I have a fixed table for this. A Tally table is just a table of sequential numbers.

;With Tally As
    (
        Select ROW_NUMBER() OVER ( ORDER BY object_id ) As Num
        From sys.columns
    )
    , IndividualChars As
    (
        Select MX.Id, Substring(MX.AlphaValue, Num, 1) As CharValue, Num
        From Tally
            Cross Join MixedValues As MX
        Where Num Between 1 And Len(MX.AlphaValue)
    )
Select MX.Id, MX.AlphaValue
    ,   Replace(
                    (
                    Select '' + CharValue
                    From IndividualChars As IC
                    Where IC.Id = MX.Id
                        And PATINDEX('[ 0-9]', CharValue) > 0
                    Order By Num
                    For Xml Path('')
                    )
                , ' ', ' ') As NewValue
From MixedValues As MX

From a top level, the idea here is to split the string into one row per individual character, test the type of pattern you want and then re-constitute it.

Note that my sys.columns table only contains 500 some odd rows. If you had strings larger than 500 characters, you could simply cross join sys.columns to itself and get 500^2 rows. In addition, For Xml Path returns a string with spaces escaped (note the space in my pattern index [ 0-9] which tells the system to include spaces.) so I use the replace function to reverse the escaping.

EDIT: Btw, this will only work in SQL 2005+ because of my use of the CTE. If you wanted a SQL 2000 solution, you would need to break up the CTE into separate table creation calls (e.g. Temp tables) but it could still be done.

EDIT: I added the Num column in the IndividualChars CTE and added an Order By to the NewValue query at the end. Although it probably will reconstitute the string in order, I wanted to ensure that it would by explicitly ordering the results.