Post reply

Query For Lower Case f

  • June 1, 2005 at 5:53 am

    #167355

    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

  • June 1, 2005 at 6:21 am

    #562139

    This would look something like this :

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

  • June 1, 2005 at 6:34 am

    #562144

    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]

  • June 2, 2005 at 2:37 am

    #562434

    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.

  • June 2, 2005 at 2:44 am

    #562437

    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]

  • June 2, 2005 at 7:10 am

    #562500

    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.

  • June 2, 2005 at 9:40 am

    #562611

    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

  • June 2, 2005 at 9:42 am

    #562613

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

  • June 2, 2005 at 9:48 am

    #562619

    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.

  • June 4, 2005 at 12:49 am

    #563061

    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

  • June 4, 2005 at 12:49 am

    #563060

    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

  • June 6, 2005 at 8:30 am

    #563237

    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.

  • June 6, 2005 at 10:46 pm

    #563391

    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.

  • June 7, 2005 at 6:41 am

    #563472

    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.

  • December 11, 2013 at 4:30 am

    #1672562

    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 15 total)

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