Saviynt unveils its cutting-edge Intelligence Suite products to revolutionize Identity Security!
Click HERE to see how Saviynt Intelligence is transforming the industry.
Saviynt Copilot Icon

SQL Replace is working properly(Case sensitive)

Shubhamjain27
Regular Contributor II
Regular Contributor II

Hi,

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

Shubhamjain27_0-1720000981415.png

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.

Shubhamjain27_1-1720001072473.png

Since when SQL is a case sensitive language?

6 REPLIES 6

NM
Honored Contributor II
Honored Contributor II

Hi @Shubhamjain27 , replace function is 

Try this should solve your issue

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

Shubhamjain27
Regular Contributor II
Regular Contributor II

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

NM
Honored Contributor II
Honored Contributor II

What is your use case??

Amit_Malik
Valued Contributor II
Valued Contributor II

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.
Kind Regards,
Amit Malik
If this helped you move forward, please click on the "Kudos" button.
If this answers your query, please select "Accept As Solution".

Shubhamjain27
Regular Contributor II
Regular Contributor II

on W3schools its working :

Shubhamjain27_0-1720005740215.png

I will change the syntax accordingly.

Thanks

SumathiSomala
All-Star
All-Star

@Shubhamjain27 REPLACE performs a case-sensitive replacement.

Regards,
Sumathi Somala

If this reply answered your question, please Accept As Solution and give Kudos.