CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Chris
Chris

Reputation: 1027

Replace and Convert

I made a script to import data from external file to a SQL Table (SQL 2012). The first column of the external file was only number and the column of the SQL table is INT.

Now, in the external file, they have insert alpha numeric data like '6B32E' and it didn't synchronise correctly anymore.

I can't change the SQL column's type.

Is it possible to replace and convert as INT that kind of string. ? My idea is to replace the alphabet letter by their place number in the alphabet.

In the example '6B32E' will becoming '62325'

Upvotes: 0

Views: 86

Answers (1)

Rich Benner
Rich Benner

Reputation: 8113

You could use REPLACE (26 times). It's not going to be pretty but it'll work. It will start something like this;

Test Data

CREATE TABLE #TestData (TextField nvarchar(5))
INSERT INTO #TestData (TextField)
VALUES
('6B32E')
,('A6E52')
,('58C69')

Query

SELECT
    TextField
    ,REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TextField,'A',1),'B',2),'C',3),'D',4),'E',5) IntField
FROM #TestData

Output

TextField   IntField
6B32E       62325
A6E52       16552
58C69       58369

Upvotes: 1

Related Questions