Post reply

Case Problem

  • October 27, 2003 at 9:49 pm

    #82462

    I have two records is a DB

    in Record1 it has 'A' in Field1

    in Record2 it has 'a' in Field1

    When I do a query Where Field1 ='A' it is returning both records

    I want only to return 'a' record

    How can I do this




    My Blog: http://dineshasanka.spaces.live.com/

  • October 27, 2003 at 10:10 pm

    #479796

    Set NoCount On

    Create Table #X(N Char(1))

    Insert Into #X Values('A')

    Insert Into #X Values('a')

    Select

    *

    From #X

    Where ASCII(N) = 65 --Use the value rather than convert it ASCII('A')

    --Select * From #x

    Drop Table #X

    Not sure how this will cope on really large tables.

    Cheers,

    Crispin

    Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

  • October 27, 2003 at 10:13 pm

    #479797

    Actully solution should be valid for any no of charators

    'a' is an example it can have more charactors

    User is entering the value which he want to select

    depending on that value I have to do a search

    Thank you




    My Blog: http://dineshasanka.spaces.live.com/

  • October 27, 2003 at 10:21 pm

    #479798

    Converting it to binary and comparing that would also work on a string.

    ASCII only retuyrns the first char's value.

    Set NoCount On

    Create Table #X(N Varchar(100))

    Insert Into #X Values('Crispin')

    Insert Into #X Values('crispin')

    Insert Into #X Values('crispiN')

    Select

    *

    From #X

    Where Convert(Binary, N) = Convert(Binary, 'CrispiN')

    --Select * From #x

    Drop Table #X

    Cheers,

    Crispin

    Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

  • October 27, 2003 at 11:13 pm

    #479800

    Great!!!

    My field is nvarchar

    Select [Description]

    From alarmLogic

    where

    Convert(Binary,Convert(varchar, [Description]))= Convert(Binary, 'below')

    is working for me

    Thankx




    My Blog: http://dineshasanka.spaces.live.com/

  • October 27, 2003 at 11:15 pm

    #479801

    Do u know any similar function for access




    My Blog: http://dineshasanka.spaces.live.com/

  • October 27, 2003 at 11:19 pm

    #479802

    Access =

    Is it really necessary for a NVarchar? Would a normal varchar not suffice?

    Cheers,

    Crispin

    Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

  • October 27, 2003 at 11:21 pm

    #479803

    Yes Bcos We are saving multi bite charctors

    So we need field to be in nvarchar




    My Blog: http://dineshasanka.spaces.live.com/

  • October 27, 2003 at 11:35 pm

    #479804

    Instead of using a double convert, why not just add a N infront.

    eg:

    Convert(Binary, N'below')

    Not sure if this does a convert in the background (Might do) but if not, it does away with the double convert. You don't have to convert the NVarchar to varchar then to binary.

    Set NoCount On

    Create Table #X(N NVarchar(100))

    Insert Into #X Values('Crispin')

    Insert Into #X Values('crispin')

    Insert Into #X Values('crispiN')

    Select

    *

    From #X

    Where Convert(Binary, N) = Convert(Binary, N'crispiN')

    --Select * From #x

    Drop Table #X

    Cheers,

    Crispin

    Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

Viewing 9 posts - 1 through 8 (of 8 total)

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