Query For Lower Case f

  • 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

  • This would look something like this :

    Select * from dbo.YourTable where YourCol = '123456f' collate LATIN1_GENERAL_CS_AC

  • 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/[/url]

  • 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.

  • Please post sample data.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • 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.

  • 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

  • ... Unless he wants to change the data and never ahve to worry about his again. But that's his choice to make.

  • 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.

  • 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

  • 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

  • You can always run this query to update the data

    Update dbo.YourTable set YourColumn = UPPER(YourColumn)

    then I would put an on insert/update and run the same query for that column.

  • I tried that command and it gives me all the file numbers with small f.If i want to update the filenumber by upper case F,it is going through all the data and updates all the file number as they are already with upper case F.Can you tell me how we can update only the filenumbers with small f by using valbinary clause.

    Thanks for your help.

  • Can you post the table definition of the table (telling which field needs to be updated)? I'll be able to give you the query then.

    Also I meant this in my previous post :

    then I would put an on insert/update TRIGGER and run the same query for that column.

  • Can you check the below code, you can only update which are in Lower case.

    DECLARE @tblName TABLE

    (

    NAME VARCHAR(100),

    Action CHAR(1)

    )

    INSERT INTO @tblName

    SELECT '090909f','N'

    UNION ALL

    SELECT '090909F','N'

    Update @tblName SET name = UPPER(name),Action = 'U' where name COLLATE SQL_Latin1_General_CP1_CS_AS = Lower(name)

    select * from @tblName where Action = 'U'

    Regards,
    Mitesh OSwal
    +918698619998

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply