Querying But Considering the Cases

[charmedcharmer]

Hi guys,

I was hoping you could help me. I have a problem regarding querying records but I need to consider the cases of the characters.

For example, the 'text_msg' field has 'Hello' and 'hello' on it.

I want to query something like this:
select * from <table> where text_msg = 'Hello'
(which returns records with 'Hello' and exclude 'hello' records

and
select * from <table> where text_msg = 'hello'
(which returns records with 'hello and exclude 'Hello' records.

Thanks in advance

[kevchadders]

Using T-SQL (based on SQL Server) i would use a combination of the LEFT function and the ASCII funtion to get the ASCII value of the first character.

Then you can evalute what to do. See example below

DECLARE @Test1 AS VARCHAR(10)

SET @Test1 = 'Hello'

SELECT ASCII(LEFT(@Test1,1))

IF ASCII(LEFT(@Test1,1)) BETWEEN 65 AND 90
BEGIN 
SELECT 'Uppercase'
-- You would place SELECT code in here for UPPERCASE search
END

IF ASCII(LEFT(@Test1,1)) BETWEEN 97 AND 122
BEGIN
SELECT 'Lowercase'
-- You would place SELECT code in here for LOWER search
END


-- Examples wherer you would use it in the WHERE clause
-------------------------------------------------------

-- Where the first Character it NOT Lower Case
SELECT @Test1
WHERE NOT(ASCII(LEFT(@Test1,1)) BETWEEN 97 AND 122)

-- Where the first Character it NOT Upper Case
SELECT @Test1
WHERE NOT(ASCII(LEFT(@Test1,1)) BETWEEN 65 AND 90)

/*
ASCII Ranges
65 - 90 Upper Case
97 - 122 Lower Case
*/

Run all the code to see the results. Notice that if you change

SET @Test1 = 'Hello'

to

SET @Test1 = 'hello'

The results of the bottom two SELECT's change

[anhn]

select * from <table> where LOWERCASE(text_msg) = 'hello'

Not sure what DB language you use.
Replace LOWERCASE() with equivalent function of your DB language, such as LCase() in Access.

[mendhak]

Use COLLATE.

WHERE text_msg = 'Hello' COLLATE SQL_Latin1_General_CP1_CS_AS

[charmedcharmer]

Let me check on that now. Thanks. Ill go back with results. Thanks