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
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 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 (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)

Group: General Forum Members
Points: 153506 Visits: 9672
This would look something like this :

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

Group: General Forum Members
Points: 42747 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
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 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-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42747 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 (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)

Group: General Forum Members
Points: 153506 Visits: 9672
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 Eights!
SSC Eights! (858 reputation)SSC Eights! (858 reputation)SSC Eights! (858 reputation)SSC Eights! (858 reputation)SSC Eights! (858 reputation)SSC Eights! (858 reputation)SSC Eights! (858 reputation)SSC Eights! (858 reputation)

Group: General Forum Members
Points: 858 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 (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)SSC Guru (153K reputation)

Group: General Forum Members
Points: 153506 Visits: 9672
... 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 Eights!
SSC Eights! (858 reputation)SSC Eights! (858 reputation)SSC Eights! (858 reputation)SSC Eights! (858 reputation)SSC Eights! (858 reputation)SSC Eights! (858 reputation)SSC Eights! (858 reputation)SSC Eights! (858 reputation)

Group: General Forum Members
Points: 858 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
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 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