Case Sensitive Replace - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Case Sensitive Replace

Author

Topic

adlo
Posting Yak Master

108 Posts

Posted - 2004-12-10 : 11:02:16

The replace statement is not Case Sensitive.
I need the replace statement to take case into account.

E.g.
SELECT REPLACE('SQL','sql','Do not display me!')

should return 'SQL'

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-10 : 11:15:48

SELECT REPLACE('SQL','sql','Do not display me!' collate latin1_general_bin)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-12-10 : 11:16:37

depends on collation:


declare @temp table (col1 varchar(50) collate SQL_Latin1_General_CP1_CS_AS) -- try it with and without the part in bold
insert into @temp
select '12345abc' union all
select 'SQL' union all
select '12abc'

SELECT *
from @temp

SELECT REPLACE(col1,'sql','Do not display me!') 
from @temp

Go with the flow & have fun! Else fight the flow

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-12-10 : 11:17:21

by a minute...

Go with the flow & have fun! Else fight the flow

adlo
Posting Yak Master

108 Posts

Posted - 2004-12-10 : 11:24:46

Thanks.

Also, taking the original question into account, is it possible to do the following without doing two replaces.
Replace 'John' with 'Name'
Replace 'john' with 'name'
(Replacement value case must be same as value to be replaced)

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-10 : 11:26:49

Without any deep thought I would guess no.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-12-10 : 11:48:17

quote:
Originally posted by adlo

Thanks.

Also, taking the original question into account, is it possible to do the following without doing two replaces.
Replace 'John' with 'Name'
Replace 'john' with 'name'
(Replacement value case must be same as value to be replaced)

You could nest the replaces together so you will only need to do one update statement instead of two.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-12-10 : 13:10:23

silly... but it works


Declare @myTable table (name varchar(20))
Insert Into @myTable
Select 'John uppercase 1'
Union Select 'john lowercase'
Union Select 'JOHN uppercase all'

Declare @newName varchar(10)
Set @newName = 'kelly'

Select Replace(name,'john',case when Ascii(left(name,1)) between 65 and 90 and Ascii(right(left(name,2),1)) between 65 and 90 then upper(@newName) when Ascii(left(name,1)) between 65 and 90 then upper(Left(@newName,1)) + lower(Right(@newName,len(@newName)-1)) else lower(@newName) end), name from @myTable

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources