May 29, 2007 at 9:39 am
Why does this code seem to work when the first term (PARCEL_NO)is a char(18) and the second (PID)a varchar(10)?
UPDATE dbo.PAR_PEO
SET dbo.PAR_PEO.NAME = OUTHOUSE.dbo.KCA_RP_ACCT.TAXPAYERNAME,
dbo.PAR_PEO.THE_DATE = GETDATE()
FROM dbo.PAR_PEO
JOIN OUTHOUSE.dbo.KCA_RP_ACCT
ON (dbo.PAR_PEO.PARCEL_NO = OUTHOUSE.dbo.KCA_RP_ACCT.PID)
WHERE PARCEL_NO LIKE '334390%';
GO
SELECT PARCEL_NO,NAME, THE_DATE FROM PAR_PEO
WHERE PARCEL_NO LIKE '33439036%';
GO
May 29, 2007 at 12:33 pm
I don't think it is implict trim because Char leaves blank spaces while Vchar expands to fill the spaces, so you may have 8 unused spaces in the Char column.
Kind regards,
Gift Peddie
May 29, 2007 at 2:01 pm
I know the CHAR is right padded with 8 spaces, and the varchar is not. But the = comparison works and returns the rows.
May 29, 2007 at 2:04 pm
For comparison they would be equal, spaces or no spaces. At least I think you are asking about the below:
declare
@char char(18), @varchar varchar(10)
set
@char = 'abcde '
set
@varchar = 'abcde'
select
'1'+@char+'2' -- just to show what is in var
select
'1'+@varchar+'2'
if
@char = @varchar
print 'equal'
else
print 'not equal'
As you can see those two will be equal no matter how many spaces you put in.
May 29, 2007 at 3:14 pm
Thats heresy to an old Oracle dog like me trying to learn new tricks. In Larry's world if both are char then "abc " = "abc" and trailing blanks are ignored, but if the second is a varchar then "abc " > "abc" and the trailing blanks are significant.
May 30, 2007 at 4:13 am
The trailing spaces are not significant in varchar too. Even they are not included in string functions like LEN a CHARINDEX, so it is quite tricky to find out that particular varchar has trailing spaces.
Modified sample:
declare
@varchar1 varchar(10), @varchar2 varchar(10)
set
@varchar1 = 'abcde '
set
@varchar2 = 'abcde'
select
'1'+@varchar1+'2',LEN(@varchar1),CHARINDEX(@varchar1,' ',0) -- just to show what is in varchar1
select
'1'+@varchar2+'2',LEN(@varchar2),CHARINDEX(@varchar2,' ',0)
if
@varchar1 = @varchar2
'1: equal'
else
'1: not equal'
if
@varchar1 + '2' = @varchar2 + '2'
'2: equal'
else
'2: not equal'
May 30, 2007 at 5:54 am
Agreed Jiri it can be quite tricky. Which is why I include the leading and trailing concatenation when I look at strings during investigations.
May 30, 2007 at 6:58 am
1. Due to data type precedence the char column will be implicitly converted to varchar
2. Varchar comparisons ignore trailing spaces
3. Therefore if both columns contains the same chars, excluding trailing spaces, then they are equal
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply