ansaurus

Question

How to Replace Multiple Characters in SQL?

Answer 1

+7 A:

I would seriously consider making a CLR UDF instead and using regular expressions (both the string and the pattern can be passed in as parameters) to do a complete search and replace for a range of characters. It should easily outperform this SQL UDF.

Cade Roux 2009-10-16 19:45:57

+1: awesomeness.

Juliet 2009-10-16 20:14:22

Answer 2

+2 A:

One useful trick in SQL is the ability use @var = function(...) to assign a value. If you have multiple records in your record set, your var is assigned multiple times with side-effects:

declare @badStrings table (item varchar(50))

INSERT INTO @badStrings(item)
SELECT '>' UNION ALL
SELECT '<' UNION ALL
SELECT '(' UNION ALL
SELECT ')' UNION ALL
SELECT '!' UNION ALL
SELECT '?' UNION ALL
SELECT '@'

declare @testString varchar(100), @newString varchar(100)

set @teststring = 'Juliet ro><0zs my s0x()rz!!?!one!@!@!@!'
set @newString = @testString

SELECT @newString = Replace(@newString, item, '') FROM @badStrings

select @newString // returns 'Juliet ro0zs my s0xrzone'

Juliet 2009-10-16 19:47:32

This is very cool - how does one incorporate this inside the where clause in my question above ? – thanks

kiev 2009-10-16 20:38:13

@kiev: you can't put this in a WHERE clause.

Peter 2009-10-16 20:55:37

@kiev: creating a user-defined function is the correct approach. However, you are better off using my approach rather than nesting a bajillion replaces in one another, since my approach supports an indefinite number of replaces. You can make the function more dynamic by passing in a comma-separated list of strings to replace, using a split function (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648) to convert the list into a table, then returning the replaced string.

Juliet 2009-10-16 21:13:01

Answer 3

A:

One option is to use a numbers/tally table to drive an iterative process via a pseudo-set based query.

The general idea of char replacement can be demonstrated with a simple character map table approach:

create table charMap (srcChar char(1), replaceChar char(1))
insert charMap values ('a', 'z')
insert charMap values ('b', 'y')


create table testChar(srcChar char(1))
insert testChar values ('1')
insert testChar values ('a')
insert testChar values ('2')
insert testChar values ('b')

select 
coalesce(charMap.replaceChar, testChar.srcChar) as charData
from testChar left join charMap on testChar.srcChar = charMap.srcChar

Then you can bring in the tally table approach to do the lookup on each character position in the string.

create table tally (i int)
declare @i int
set @i = 1
while @i <= 256 begin
    insert tally values (@i)
    set @i = @i + 1
end

create table testData (testString char(10))
insert testData values ('123a456')
insert testData values ('123ab456')
insert testData values ('123b456')

select
    i,
    SUBSTRING(testString, i, 1) as srcChar,
    coalesce(charMap.replaceChar, SUBSTRING(testString, i, 1)) as charData
from testData cross join tally
    left join charMap on SUBSTRING(testString, i, 1) = charMap.srcChar
where i <= LEN(testString)

2009-10-16 20:46:27

Answer 4

A:

I don't know why Charles Bretana deleted his answer, so I'm adding it back in as a CW answer, but a persisted computed column is a REALLY good way to handle these cases where you need cleansed or transformed data almost all the time, but need to preserve the original garbage. His suggestion is relevant and appropriate REGARDLESS of how you decide to cleanse your data.

Specifically, in my current project, I have a persisted computed column which trims all the leading zeros (luckily this is realtively easily handled in straight T-SQL) from some particular numeric identifiers stored inconsistently with leading zeros. This is stored in persisted computed columns in the tables which need it and indexed because that conformed identifier is often used in joins.

Cade Roux 2009-10-17 02:58:29

ansaurus

tags:

views:

answers:

How to Replace Multiple Characters in SQL?

related questions