SQL Replace is working properly(Case sensitive)

[Shubhamjain27]

Hi,

We have to replace and its not working as its an exact match based on case sensitive:

select REPLACE('North Canton OH Retail' , 'REtail' , ''), it is not giving the exact result.

select REPLACE('North Canton OH Retail' , 'Retail' , ''), it is working good.

Since when SQL is a case sensitive language?

[NM · Accepted solution]

Hi @Shubhamjain27 , replace function is 

Try this should solve your issue

select REPLACE(lower(North Canton OH Retail), lower(REtail), '')

[Shubhamjain27]

It will yes but it will also get result in lower case. The result should be in the same format.

[NM]

What is your use case??

[Amit_Malik · Accepted solution]

Hi @Shubhamjain27 ,

Replace function is case sensitive. 

Remember, the REPLACE SQL function is case-sensitive. If your database collation is case-sensitive and you wish to perform a case-insensitive replacement, you might need to use additional functions specific to your SQL dialect (like LOWER() or UPPER()) to standardize string case before replacement.

https://www.w3schools.com/sql/func_mysql_replace.asp#:~:text=The%20REPLACE()%20function%20replaces,p....

Definition and Usage

The REPLACE() function replaces all occurrences of a substring within a string, with a new substring.

Note: This function performs a case-sensitive replacement.

Syntax

REPLACE(string, from_string, new_string)

Thanks, Amit

If this answers your query, Please ACCEPT SOLUTION and give KUDOS.

[Shubhamjain27]

on W3schools its working :

I will change the syntax accordingly.

Thanks

[SumathiSomala]

@Shubhamjain27 REPLACE performs a case-sensitive replacement.