CODEWITHSUNDEEP
sql-serverreplace
user1102550
user1102550

Reputation: 543

Ignore case in SQL Server replace

Is the anyway to ignore case in SQL server's replace so:

Declare @replaceword = 'test'

REPLACE(@var,@replaceword,'<i>' + @replaceword + '</i>')

So if @var was 'Test test'

It world return 

'<i>Test</i> <i>test</i>'

Upvotes: 2

Views: 8028

Answers (2)

dcaswell
dcaswell

Reputation: 3167

I think you're going to have to do something like this:

Declare @replaceword1 varchar(100)
Declare @replaceword2 varchar(100)

select @replaceword1 = 'test'
select @replaceword2 = 'Test'


select REPLACE(
    REPLACE('Test test',@replaceword1 COLLATE SQL_Latin1_General_Cp1_CS_AS,'<i>' +
    @replaceword1 + '</i>'),
    @replaceword2 COLLATE SQL_Latin1_General_Cp1_CS_AS,'<i>' + @replaceword2 + '</i>')

Note: This forces a case-sensitive replace: SQL_Latin1_General_Cp1_CS_AS

CS in a collation name refers to case-sensitive.

CI in a collation name refers to case-insensitive.

For more information on collation naming.

Upvotes: 3

Joel Coehoorn
Joel Coehoorn

Reputation: 415600

How REPLACE() matches things is determined by the specific collation in use. Different collations may have different comparison rules. If you take a look at the REPLACE() documentation, you'll see an example near the bottom of how to specify a specific collation:

SELECT REPLACE('Das ist ein Test' COLLATE Latin1_General_BIN, 'Test', 'desk' )

I'm not familiar enough with the different possible collations to know which you will need, but this should point you down the correct path.

Unfortunately, I suspect this still may not be enough. When you replace, you really do replace the matched text. You don't merge the matched text.

Upvotes: -1

Related Questions