T-SQL Query That Returns lower case Results Only
I have a table named "KEYWORDS" with a column named "ENTRY" of VARCHAR(10). Would it be possible to retrieve only the lower-case entries from that table?
For example, the table might look like this:
ENTRY
===========
SearchString
Searchstring
searchstring
SEARCHSTRING
And I would like to be able to run a query that looks similar to:
SELECT ENTRY FROM KEYWORDS WHERE ENTRY <condition to retun only the lowercase entry>
开发者_如何学运维
where the result of the above would be: searchstring
And if that can be done, then I would like to be able to retrieve only the ProperCase entries next. This is a hosted SQL Server 2005 database on GoDaddy.com hosting so I dont know much about how its configured. I do not have permission to run EXEC sp_help DatabaseName
I will reference this page: http://blog.sqlauthority.com/2007/04/30/case-sensitive-sql-query-search/ which was interesting to read because its similar to what I am looking for but different in that the person running the query knows what they are looking for.
I would like ALL entries that are lowercase.
Try this:
where Entry COLLATE Latin1_General_CS_AS = Lower(entry)
This works ...
create table #t (entry varchar(10))
insert #t values ('hello'), ('Hello'), ('HELLO')
select * from #t where cast(entry as varbinary(max)) = cast(LOWER(entry) as varbinary(max))
/*
entry
----------
hello
*/
The below worked for me in SSMS 2012. I used the Lower that was posted above but not the COLLATE.
select Lower(varchar or text) from Table
my learning curve with SQL has died a bit but I'm still learning a great deal.
精彩评论