Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Query For Lower Case f Expand / Collapse
Author
Message
Posted Wednesday, June 01, 2005 5:53 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 06, 2012 2:24 PM
Points: 31, Visits: 51
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
Post #186509
Posted Wednesday, June 01, 2005 6:21 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, January 16, 2014 12:03 PM
Points: 21,376, Visits: 9,594
This would look something like this :

Select * from dbo.YourTable where YourCol = '123456f' collate LATIN1_GENERAL_CS_AC
Post #186514
Posted Wednesday, June 01, 2005 6:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 1:18 AM
Points: 5,956, Visits: 285

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/
Post #186519
Posted Thursday, June 02, 2005 2:37 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 06, 2012 2:24 PM
Points: 31, Visits: 51
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.
Post #186889
Posted Thursday, June 02, 2005 2:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 1:18 AM
Points: 5,956, Visits: 285
Please post sample data.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/
Post #186892
Posted Thursday, June 02, 2005 7:10 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, January 16, 2014 12:03 PM
Points: 21,376, Visits: 9,594
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.
Post #186972
Posted Thursday, June 02, 2005 9:40 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 06, 2012 2:48 PM
Points: 182, 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
Post #187099
Posted Thursday, June 02, 2005 9:42 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, January 16, 2014 12:03 PM
Points: 21,376, Visits: 9,594
... Unless he wants to change the data and never ahve to worry about his again. But that's his choice to make.
Post #187101
Posted Thursday, June 02, 2005 9:48 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 06, 2012 2:48 PM
Points: 182, 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.
Post #187107
Posted Saturday, June 04, 2005 12:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 06, 2012 2:24 PM
Points: 31, Visits: 51
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
Post #187632
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse