#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2010
    Posts
    12
    Rep Power
    0

    Comparing NVARCHAR


    I am scratching my head here.

    I have a table where the Primary key is a (vend_num,NVARCHAR(7))
    we use this as vendor number.
    Each vendor has 2 vend_num -
    1 that is only a number (400141) and one that is prefixed with a letter (R400141)

    I am trying to copy 1 field from the R-vend_num to the non R-Vend_num

    Code:
    UPDATE vendor SET
    purch_lst_yr = (SELECT purch_ytd FROM vendor WHERE vend_num =  'R400141')
    where vend_num = '400141'
    Problem is it copies a null value even though there is data in the filed.

    Also some strange select query behavior.

    if I use
    Code:
    select * from vendor where vend_num like '%400141'
    I get both vendor lines.

    but if I use
    Code:
    select * from vendor where vend_num = '400141'
    I get nothing

    but if I use
    Code:
    select * from vendor where vend_num = 'R400141'
    I get the R-vend_num

    What am I missing. Is this typical? How can I get around this. Or is there a problem with my table.
  2. #2
  3. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2005
    Posts
    724
    Rep Power
    295
    What does the following code return?
    Code:
    select * from vendor where vend_num like '400141'
    EDIT

    If that returns nothing, try this
    Code:
    select * from vendor where vend_num like ' 400141'

    Comments on this post

    • keithschm agrees
    Last edited by Vomster; January 20th, 2011 at 08:35 AM.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2010
    Posts
    12
    Rep Power
    0
    Originally Posted by Vomster
    If that returns nothing, try this
    Code:
    select * from vendor where vend_num like ' 400141'
    worked like a charm. when I use the space in the update query it works as well. How come?

    In my earlier tests I tried ltrim and that did not work. What is going on here.

    Thank you very much for your input. I hate scratching my head foe hours.
  6. #4
  7. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2005
    Posts
    724
    Rep Power
    295
    It worked for me by doing ltrim(vend_num).

    Try
    Code:
    ltrim(rtrim(vend_num))

IMN logo majestic logo threadwatch logo seochat tools logo