Replace a capital letter in a string

Question

Post reply

Replace a capital letter in a string

pvong

SSC Eights!

Points: 832
More actions
April 6, 2016 at 1:41 pm

#326310

Say I have a value in a table = "brkXa"
I need a select statement that will replace the capital X with / so the result should look like this "brk/a". I do not want it to replace lower case x with /. So I don't want "nsx" to come back as "ns/".
I've tried these..
Select REPLACE(value, cast('X' as varbinary), '/') AS Test From table
I get brk/a but I also get ns/
Select REPLACE(value, UPPER('X'), '/') AS Test From table
I get brk/a but I also get ns/
Any help is greatly appreciated.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 1

Try this,

REPLACE(value COLLATE Latin1_General_Bin, 'X', '/')

Alan Burstein SSC Guru Points: 61141 More actions · Answer 2

You could also do this:

REPLACE(value, char(88), '/')

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

pvong SSC Eights! Points: 832 More actions · Answer 3

Alan.B (4/6/2016)
You could also do this:
REPLACE(value, char(88), '/')

This did not work. Gave me the same results.

pvong SSC Eights! Points: 832 More actions · Answer 4

pvong

SSC Eights!

Points: 832

April 7, 2016 at 7:40 am

#1870475

Thanks ZZartin, that worked perfectly.

drew.allen SSC Guru Points: 76985 More actions · Answer 5

Alan.B (4/6/2016)
You could also do this:
REPLACE(value, char(88), '/')

The replacement depends on the collation, and the default collation is case insensitive. Using the CHAR() function, does not change the default collation, so it does not change the behavior of the replace.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Alan Burstein SSC Guru Points: 61141 More actions · Answer 6

drew.allen (4/7/2016)
Alan.B (4/6/2016)
You could also do this:
REPLACE(value, char(88), '/')
The replacement depends on the collation, and the default collation is case insensitive. Using the CHAR() function, does not change the default collation, so it does not change the behavior of the replace.
Drew

Yep. I was not able to test my code when I posted it.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001