locked
I dont want to remove the leading and trailing spaces in the varchar variable. RRS feed

  • Question

  • User-74539191 posted

    My situation is that when user enters the password with trailing blank spaces sqlserver 2008 automatically removing the trailing blank spaces and I dont want to remove that for that what i have to write in sp which is validating the user id and password any help would be appreciated.

    Monday, January 2, 2012 11:31 PM

Answers

  • User3866881 posted

    its displaying not same even

    Yes,that's just what you need!And my example has proved a theory that char's data bytes is quite different from that of varchar,because it will trim all the empty blanks from its tail。But if you want ignore the head and tail's spaces,you can still use char instead of varchar(in this way tail spaces cannot be removed automatically,and you can do comparation)use LTrim or Rtrim to deal with this:

    declare @nm char(100)
    set @nm=' zee '
    if rtrim(ltrim(@nm)) =LTRIM(rtrim('zee'))
    print 'same'
    else
    print 'not same'
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 5, 2012 8:26 PM

All replies

  • User522486851 posted

    that can be happen when you using varchar data type use char data type.

    Monday, January 2, 2012 11:34 PM
  • User-74539191 posted

    Thanks for reply,

     I have tried using char also, but the same problem more over I am using SQL Server 2008 R2.

    If you found the solution, so may you please post the script so that I can test on my system/database.

    Tuesday, January 3, 2012 12:08 AM
  • User522486851 posted

    There is no problme with SQL2008 char shold be work.

    can you post your SP and Table Structure here,

    Tuesday, January 3, 2012 12:14 AM
  • User-74539191 posted

    I m just sharing one simple script here 

    declare @nm char(100)
    set @nm='zee  '
    if @nm = 'zee'
    print 'same'
    else
    print 'not same'
    
    out put...
    same
    Tuesday, January 3, 2012 10:21 AM
  • User3866881 posted

    Hello ZeenshanAnsar:)

    You should use DataLength to check how many bytes they have:

    SET ANSI_PADDING on
    declare @nm char(100)
    set @nm='zee '
    if DATALENGTH(@nm) = DATALENGTH('zee')
    print 'same'
    else
    print 'not same'
    Wednesday, January 4, 2012 8:46 PM
  • User-74539191 posted

    its displaying not same even

    Thursday, January 5, 2012 11:06 AM
  • User3866881 posted

    its displaying not same even

    Yes,that's just what you need!And my example has proved a theory that char's data bytes is quite different from that of varchar,because it will trim all the empty blanks from its tail。But if you want ignore the head and tail's spaces,you can still use char instead of varchar(in this way tail spaces cannot be removed automatically,and you can do comparation)use LTrim or Rtrim to deal with this:

    declare @nm char(100)
    set @nm=' zee '
    if rtrim(ltrim(@nm)) =LTRIM(rtrim('zee'))
    print 'same'
    else
    print 'not same'
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 5, 2012 8:26 PM