CODEWITHSUNDEEP
sqlsql-serversubstring
Guillermo Siles Bonilla
Guillermo Siles Bonilla

Reputation: 61

SQL upper/lower + substring not working well

I have a table with a column named "name" between other columns. I was making experiments with sql functions and I don't know why this doesn't work. When I run this code:

select name , upper(SUBSTRING(name,1,1)), SUBSTRING(name,1,1) from primeraTabla

I get a table like:

name     (no column name)    (no column name)
paco     P                   p
Fernando F                   F
....

But when I try:

select * from primeraTabla where upper(SUBSTRING(name,1,1)) = SUBSTRING(name,1,1)

the first row with name paco should be excluded, but it is not. The filter doesn't work.

So the question is, why this filter doesn't work?

Upvotes: 2

Views: 2041

Answers (2)

Wei Lin
Wei Lin

Reputation: 3811

Try it use "ASCII"

select * from primeraTabla 
where ascii(upper(SUBSTRING(name,1,1))) = ascii(SUBSTRING(name,1,1))

Result:

name
--------------------
Fernando

db<>fiddle Demo Link

Upvotes: 1

Ullas
Ullas

Reputation: 11556

You have to use COLLATE Latin1_General_CS_AS to make the search case sensitive.

Query

select * from primeraTabla 
where upper(SUBSTRING(name,1,1)) COLLATE Latin1_General_CS_AS = SUBSTRING(name,1,1);

Even you can change the collation of the any column for any table permanently by,

Query

ALTER TABLE [your_table_name]
ALTER COLUMN [your_column_name] VARCHAR(100)
COLLATE Latin1_General_CS_AS

Upvotes: 0

Related Questions