string comparison problem

Question

Post reply

string comparison problem

Jonathan Matt

SSCarpal Tunnel

Points: 4027
More actions
June 25, 2007 at 7:52 am

#117232

Hello all,
The following update statement in a script:
UPDATE
#TempRecordsToCreate
SET
#TempRecordsToCreate.ShortName = CODE_PROGRAM.CODE_VALUE
FROM
CODE_PROGRAM
WHERE
#TempRecordsToCreate.ShortName = CODE_PROGRAM.MEDIUM_DESC
I am trying to update ShortName in a temp table with CODE_VALUE from a database table where ShortName = MEIDUM_DESC. It does what I expect as long as there are no spaces in the ShortName string. As an example ShortName 'Amman' becomes CODEVAUE 'AMMAN' as expected but 'Paris CFS' does not become 'PARCSF'.
I have tried rewriting the UPDATE to use a subselect:
UPDATE
#TempRecordsToCreate
SET
#TempRecordsToCreate.ShortName = (SELECT a.CODE_VALUE FROM CODE_PROGRAM a JOIN #TempRecordsToCreate b ON a.MEDIUM_DESC = b.ShortName)
This returns NULL in a case like 'Paris CFS'. I have tried timming, replacing the internal spaces, casting to identical datatypes etc.
Nothing works!
Why cant' SQL Server see 'Paris CFS' = 'Paris CFS' ???
Thnaks!
Jonathan

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

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

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 1

Try

UPDATE

t

SET t.ShortName = w.CODE_VALUE

FROM #TempRecordsToCreate AS t

INNER JOIN CODE_PROGRAM AS w ON w.MEDIUM_DESC = t.ShortName

If it doesn't work, please post DDL for the two tables.

N 56°04'39.16"
E 12°55'05.25"

Jonathan Matt SSCarpal Tunnel Points: 4027 More actions · Answer 2

Thanks Peter,

That does not work either! Here is the DDL

CREATE

TABLE #TempRecordsToCreate

(

ApplicantID

int,

FirstName

nvarchar(255),

LastName

nvarchar(255),

EMail

nvarchar(255),

Term

nvarchar(50),

[Year]

int,

ShortName

nvarchar(100)

)

CREATE

TABLE [dbo].[CODE_PROGRAM](

[CODE_VALUE_KEY] [varchar]

(6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[CODE_VALUE] [varchar]

(6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[SHORT_DESC] [varchar]

(10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[MEDIUM_DESC] [varchar]

(20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[LONG_DESC] [varchar]

(40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[STATUS] [varchar]

(1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[CREATE_DATE] [datetime]

NOT NULL,

[CREATE_TIME] [datetime]

NOT NULL,

[CREATE_OPID] [varchar]

(8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[CREATE_TERMINAL] [varchar]

(4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[REVISION_DATE] [datetime]

NOT NULL,

[REVISION_TIME] [datetime]

NOT NULL,

[REVISION_OPID] [varchar]

(8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[REVISION_TERMINAL] [varchar]

(4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[CODE_XVAL] [varchar]

(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[CODE_XDESC] [varchar]

(64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[ABT_JOIN] [varchar]

(1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[FTE_CREDITS] [numeric]

(6, 2) NOT NULL,

[PROGRAM_TYPE] [varchar]

(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

CONSTRAINT [C_PROGRAM_PK] PRIMARY KEY CLUSTERED

(

[CODE_VALUE_KEY]

ASC

)

ON [PRIMARY]

)

ON [PRIMARY]

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 3

One reason might be that you are JOINING on varchar and nvarchar?

Which collation do the temporary table use?

N 56°04'39.16"
E 12°55'05.25"

Jonathan Matt SSCarpal Tunnel Points: 4027 More actions · Answer 4

Both the temp db and the db of CODE_PROGRAM table have collation = SQL_Latin1_General_CP1_CI_AS.

I have also tried creating the temp table with a SELECT INTO so that I know the collations are the same.

I have tried to deal with the differing datatypes with:

UPDATE #TempRecordsToCreate

SET #TempRecordsToCreate.ShortName = CODE_PROGRAM.CODE_VALUE

FROM CODE_PROGRAM

WHERE #TempRecordsToCreate.ShortName = CAST(CODE_PROGRAM.MEDIUM_DESC AS nvarchar(100))

Nothing works!

Help!!!!

Jonathan

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 5

The SET statement does not allow the following...

SET #TempRecordsToCreate.ShortName = CODE_PROGRAM.CODE_VALUE

Remove the code in red and it should work fine...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110208 More actions · Answer 6

Try

SELECT ShortName , CONVERT(varbinary(200), ShortName)

FROM #TempRecordsToCreate

SELECT MEDIUM_DESC, CONVERT(varbinary(200), MEDIUM_DESC)

FROM CODE_PROGRAM

See if there is a difference not visible by eyes.

_____________
Code for TallyGenerator

noeld SSC Guru Points: 96590 More actions · Answer 7

I am pretty sure your column value has PADDING Spaces.

Try selecting DATALENGTH on your column

* Noel

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 8

Post some sample data for both tables. Also provide expected output.

N 56°04'39.16"
E 12°55'05.25"

Jonathan Matt SSCarpal Tunnel Points: 4027 More actions · Answer 9

I have found my problem!

Unknown to me a group of MEDIUM_DESC were removed from the CODE_PROGRAM table this morning. They happened to mostly be MEDIUM_DESC with internal spaces and I jumped to a false conclusion!

Thank you all for your help.

Good old SQL Server is doing exactly what it is asked to do once again.

Jonathan