Query For Lower Case f

  • Zia Khan

    SSC Veteran

    Points: 241

    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

    SSC Guru

    Points: 294069

    This would look something like this :

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

  • Frank Kalis

    SSC Guru

    Points: 111183

    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]

  • Zia Khan

    SSC Veteran

    Points: 241

    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

    SSC Guru

    Points: 111183

    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]

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    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

    SSCommitted

    Points: 1556

    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

    SSC Guru

    Points: 294069

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

  • Brian Laws

    SSCommitted

    Points: 1556

    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

    SSC Veteran

    Points: 241

    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

  • Zia Khan

    SSC Veteran

    Points: 241

    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

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    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.

  • Zia Khan

    SSC Veteran

    Points: 241

    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.

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    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.

  • Mitesh Oswal

    SSCrazy

    Points: 2798

    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