CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2005t-sql
user980191
user980191

Reputation: 61

Removing the numerics

col1 

ABX67206
ABX67245
ABX67275
ABX67312
ABX67313
ABX67333
ABX67335

excel sheet, where we have a column called policy_prefix (datatype - nvarchar).

For example: I have col1 - ABX67206 I need the output as ABX means to remove all the numeric values from the column.

some times i get the records like that col1-'ABXTU02' where I need the output as 'ABXTU'

here in the question means remove only numeric values and the length remains same

the out put is like

col1

ABX ABX ABX ABX ABX ABXTU ABXTU ABX ABXTUYT

like this

Upvotes: 2

Views: 88

Answers (3)

Igor Borisenko
Igor Borisenko

Reputation: 3866

Try this

declare @tbl table (i varchar(50))

insert into @tbl values ('qwe1456'),('w4'),('op75'),('123')

SELECT i, LEFT(i,PATINDEX('%[0-9]%',i)-1)
FROM  @tbl

Upvotes: 2

Julian
Julian

Reputation: 20324

If the text in col1 is always on the same format (3 characters, followed by any number of digits), you can use the substring function:

SELECT x = SUBSTRING('ABX67206', 1, 3);

Which will return ABX in this case.

Upvotes: 1

Kirill Polishchuk
Kirill Polishchuk

Reputation: 56222

You can use 10 times REPLACE function, i.e.:

select replace(replace(replace(column, '0', ''), '1', ''), '2', '') ... and so on

Upvotes: 1

Related Questions