SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query For Lower Case f


Query For Lower Case f

Author
Message
Zia Khan
Zia Khan
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 55
Hi All,
Plz help me out for query for the lower case data.Our company having data for file and file numbers are followed by '123456f'.And i want to get data with file numbers with lower case 'f'.
Thanks
Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69611 Visits: 9671
This would look something like this :

Select * from dbo.YourTable where YourCol = '123456f' collate LATIN1_GENERAL_CS_AC
Frank Kalis
Frank Kalis
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20249 Visits: 289

Am I understanding you correctly?

SELECT * FROM table WHERE RIGHT(column,1)=CHAR(102)

or simply

SELECT * FROM table WHERE RIGHT(column,1)='f'



--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/
Zia Khan
Zia Khan
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 55
Thanks to your reply but i still cant retrieved the files which lower case 'f'.
Accutaly some of our data entry peaople scanning the file which capslock is off, and the time of request the file comes up but at the time of prossesing scanner scanning the file labels with filenumber cap 'F' but in table the entry is lower case.I want update all the files which are lower case 'f'.Plz reply to see this criteria.
Thanks again.
Frank Kalis
Frank Kalis
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20249 Visits: 289
Please post sample data.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/
Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69611 Visits: 9671
This is just a guess of a solution that might resolve your problem. As Frank said, if it doesn't work, post some data along with the table definition and the exepected modifications.

Update dbo.YourTable set YourColumn = UPPER(YourColumn)

This will change all characters to upper case for that column.
Brian Laws
Brian Laws
SSC-Addicted
SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)

Group: General Forum Members
Points: 412 Visits: 104
I would do it like how Remi suggested. Just use the collate function and have the right capitalization in the Where clause:

To find lower case f:
select titledesc from tblproducts (nolock) where titledesc like '%f%' COLLATE SQL_Latin1_General_CP1_CS_AS

To find upper case f:
select titledesc from tblproducts (nolock) where titledesc like '%F%' COLLATE SQL_Latin1_General_CP1_CS_AS

Here's a resource you will want to check out: http://vyaskn.tripod.com/case_sensitive_search_in_sql_server.htm
Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69611 Visits: 9671
... Unless he wants to change the data and never ahve to worry about his again. But that's his choice to make.
Brian Laws
Brian Laws
SSC-Addicted
SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)

Group: General Forum Members
Points: 412 Visits: 104
Right. Exactly. I would personally opt for a more permanent solution, such as having a check constraint or even a trigger which updates it to upper case. Could also have a computed column or a view which upper cases them as well.
Zia Khan
Zia Khan
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 55
Yes,I got all the filenumber with lowercase 'f' now if i want update them how its possible and what would be the permanent solution so nobady can enter filenumber with lowercase 'f'?
Thanks my friends
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search