Records with lower case in DB
-
How to find only records with the lower case in the field?
-
Vika,
you don't say if the column you're querying contains jaust a single character or more.
In case it's just a single character you can use this:
SELECT * From table1
where ASCII(col1) between 97 and 122
If your column contains more than one character you the question is do you need to evaluate all of them or maybe jsut the first one.
Markus
[font="Verdana"]Markus Bohse[/font]
-
If you're after more than just the first character, you're going to have to do something with a COLLATE statement. If you using the default (SQL_Latin1_General_Cp1_CI_AS), you can do something like...
where lower(myfield) = myField COLLATE SQL_Latin1_General_Cp1_CS_AS
But please note that this will return empty strings, too, and will not catch things like numbers, commas, etc. in your data. Are you checking to make sure you have nothing BUT lower case letters in your field?
-
There are no empty strings. All that is needed is to catch cases with lower case letters in the field. Thanks!
-
Didn't work
Should return a record like s3BBkdLLL if at least one character is lower case. It's a more complex regular expression I guess. It can have upper case letters too but if at least one is lower case, it should be returned.
-
OK never mind
Just copied all the records into good old VI and find them all
-
You can easily compare binary streams that are the result of the conversion of the original text vs. all-upper-case text.
SELECT MyCol
FROM MyTable
WHERE CONVERT(varbinary(8000), MyCol) CONVERT(varbinary(8000), UPPER(MyCol))
MyCol is the column you'd be searching.
MyTable is the table that contains MyCol.
-
THANK YOU THANK YOU THANK YOU !
This WORKS!
-
I want to select a column and append a value to it but I get an error, why?
select column + " some text to add to result " as result
-
Can you paste you SQL stamtment here?
-
Try using single quotes
select column + ' some text ' as result
from MyTab
Is column defined as a char or varchar? If not you may need to convert to a character based data type before appending.
select convert(varchar(100),column) + ' some text ' as result
from MyTab
-
You were right, it helped!
-
this works in sqlserver 2000 gets records with lower case letters in [name] column
select
*
from
sysobjects
where
binary_checksum (name) binary_checksum(upper(name))
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply