SQLTeam.com | Weblogs | Forums

Character compare

sql2008r2

#1

Our code has been running fine for a while now. So today the SSN is giving a truncation issue.
The value is stored as VARCHAR(16) - this also handles TID values which we do not have at this time.
Anyway, the temp table is setup with VARCHAR(9) and when we try to insert yesterdays values we receive a truncation error.

I did LEN(SSN) and found all equal 9 (or NULL). Tried RTRIM but still got the error. However, LEFT(SSN,9) worked.

We would like to find the "bad" value. I executed WHERE LEFT(SSN, 9) <> SSN but no value was returned.

Thank you for your assistance,
djj


#2

Try to use

OR DATALENGTH(SSN)<> DATALENGTH(LEFT(SSN,9))

Here is a sample:

declare @vtSSN TABLE
(    SSN VARCHAR(16)
)

INSERT INTO @vtSSN(SSN)
VALUES ('ABCDEFGHJ'),('AAAAAAAAA '),('ABCDEFGHJ')

SELECT * 
FROM @vtSSN
WHERE LEFT(SSN,9) <> SSN
OR DATALENGTH(SSN)<> DATALENGTH(LEFT(SSN,9))

https://support.microsoft.com/en-us/kb/316626


#3

Thank you for your reply.

Still no luck. After posting I had tried DATALENGTH and it gave 9 or NULL, but I tried your code just to be sure and came up with no returns.


#4

That will ignore trailing spaces, whereas DATALENGTH(SSN) will include them


#5

I remembered that after the first post. However in this case DATALENGTH and LEN are giving the same number.

Could there be a special character in the field? Or would DATALENGTH count that?


#6

The example I posted , should return (on my station)

SSN
AAAAAAAAA 

Probably , different SET settings /collation
My settings:

5496
ANSI_WARNINGS
ANSI_PADDING
ANSI_NULLS
ARITHABORT
QUOTED_IDENTIFIER
ANSI_NULL_DFLT_ON
CONCAT_NULL_YIELDS_NULL

SQL_Latin1_General_CP1_CI_AS

You could use :

select * from table
where patindex('% %',collumn)>0

#7

Or just ORDER By SSN ASC and ORDER By SSN ASC , with TOP 100
to see what values do you have


#8

First let me thank @stepson and @Kristen for your help.

After further investigation I have found the problem is not necessarily with the SSN. I have stepped through the query, commenting out columns and uncommenting. That is how I found SSN, however, when I just use SSN it works. I find that one of two other columns will cause SSN to throw the error.

Here is the original:

IF OBJECT_ID (N'tempdb..#Curr', N'U') IS NOT NULL     
    DROP TABLE #Curr; 
CREATE TABLE #Curr (
    BatchGuid           uniqueidentifier,
    App                 varchar(1),
    RefNum              varchar(10),
    MemoText            varchar(40),
    NewAddr             varchar(80),
    NewPhone            varchar(10),
    [NewNamex]             varchar(50),
    SecName             varchar(26),
    POEPhone            varchar(10),
    POEName             varchar(15),
    POEAddr             varchar(40),
    CeaseDesistDate     date,
    DisputeDate         date,
    CCCInfo             varchar(20),
    ConsolidatorInfo    varchar(20),
    NewSSN              varchar(9),
    AKAName             varchar(15),
    PhoneAddrUpdateDate date
)
insert into #Curr (
    --BatchGuid,
    App,RefNum,
    NewAddr,
    NewPhone,
    [NewNamex],
    SecName,
    NewSSN)
select --(select BatchGuid from #TmpParms),
    'P',
    A.MatterNum,
    left(ISNULL(C.Addr1 + ' ', '') + ISNULL(C.Addr2 + ' ', '') + ISNULL(C.City + ' ', '') + ISNULL(C.State + ',', '') + ISNULL(C.Zip, ''), 80) AS NewAddr,
    C.Phone1 AS NewPhone,
     left(ISNULL(C.FirstName + ' ', '') + ISNULL(C.LastName, ''),26) AS [NewNamex],
    left(ISNULL(C2.FirstName + ' ', '') + ISNULL(C2.LastName, ''),26) AS SecName,
    left(C.SSN,9)  
    --C.SSN
from MyTable1 A
inner join MyTable2 C on A.MatterNum = C.MatterNum and C.ContactType = 'DECEASED'
left join MyTable3 C2 on A.MatterNum = C2.MatterNum and C2.ContactType = 'COSIGNOR'

It turns out that NewNamex or NewAddr will cause the problem when not using left(C.SSN,9)


#9

DATALENGTH() will include it, but, for example, a CHAR(9) TAB character could make the DISPLAY look wrong. As could an extended character that didn't display at all (other data to the right moved one-place-left as a result). But I don't think that is the problem you have got ...

If you've solved it them maybe it doesn;t need further discussion? but I can't see how NewNamex or NewAddr should make any difference to SSN - whether you used left(C.SSN,9) or just C.SSN

(Assuming that

SELECT MAX(DATALENGTH(SSN))
FROM   MyTable
WHERE  SSN IS NOT NULL

gives the result of 9 ?