HELP! Strange behavior using t-sql REPLACE function

I am seeing very odd behavior with the t-sql REPLACE funtion.

I am trying to wipe out the 'T' character (unicode value 222) with a space character (' ' unicode value 32) and it appears to be replacing occurrences of 'th' together as well. Here are some selects to show this behavior:

select nchar(unicode('T'))
select nchar(unicode(' '))
select unicode('T')
select unicode(' ')
select replace('Urban & Ethnic', nchar(unicode('T')), nchar(unicode(' ')))
select replace('Urban T Ethnic', nchar(unicode('T')), nchar(unicode(' ')))
select replace('Urban & Ethnic', nchar(222), nchar(32))
select replace('Urban T Ethnic', nchar(222), nchar(32))
select replace('Urban & Ethnic', 'T', ' ')
select replace('Urban T Ethnic', 'T', ' ')

NOTICE that the last two statements:

select replace('Urban & Ethnic', 'T', ' ') - RESULT 'Urban & Ethnic'
select replace('Urban T Ethnic', 'T', ' ') - RESULT 'Urban Ethnic'

...work properly, while the other ones dont:

select replace('Urban & Ethnic', nchar(unicode('T')), nchar(unicode(' '))) - RESULT 'Urban & E nic'
select replace('Urban T Ethnic', nchar(unicode('T')), nchar(unicode(' '))) - RESULT 'Urban E nic'
select replace('Urban & Ethnic', nchar(222), nchar(32)) - RESULT 'Urban & E nic'
select replace('Urban T Ethnic', nchar(222), nchar(32)) - RESULT 'Urban E nic'

This function ONLY seems to work with the character representation, any attempt to convert an integer value to a character representation fails miserably...

Please advise, and thanks for your help in advance.

It seems that the collation the active database uses treats "th" and "T" as the same character. To fix this, change the database collation to something more appropriate, or use tricks like the following in your queries:

select replace(N'Urban T Ethnic' COLLATE Latin1_General_BIN, N'T', N' ')

By forcing the collation to BIN (binary), a binary comparison is done, which doesn't do fancy character-equivilancy stuff.

-Ryan

|||

Thanks!

sp_helpsort reveals the default collation = Latin1-General, blah, blah, blah, etc...

I even tried to convert EVERYTHING to binary var types within the query and it STILL came up with the wrong result.

Thanks again for your help. I didn't even consider changing the COLLATION type in the query itself...

sql

Help! - Subscriptions won't run; here is the LOG file

2007-05-18 11:07:07 - ? [100] Microsoft SQLServerAgent version 8.00.760 (x86
unicode retail build) : Process ID
2007-05-18 11:07:07 - ? [100] Microsoft SQLServerAgent version 8.00.760 (x86
unicode retail build) : Process ID 3036
2007-05-18 11:07:07 - ? [101] SQL Server <my computer name> version 8.00.760
(0 connection limit)
2007-05-18 11:07:07 - ? [102] SQL Server ODBC driver version 3.85.1025
2007-05-18 11:07:07 - ? [103] NetLib being used by driver is DBMSSHRN.DLL;
Local host server is
2007-05-18 11:07:07 - ? [310] 1 processor(s) and 768 MB RAM detected
2007-05-18 11:07:07 - ? [339] Local computer is <my computer name> running
Windows NT 5.2 (3790)
2007-05-18 11:07:07 - ! [364] The Messenger service has not been started -
NetSend notifications will not be sent
2007-05-18 11:07:07 - ? [129] SQLSERVERAGENT starting under Windows NT
service control
2007-05-18 11:07:07 - + [260] Unable to start mail session (reason: No mail
profile defined)
2007-05-18 11:07:07 - + [396] An idle CPU condition has not been defined -
OnIdle job schedules will have no effect
2007-05-18 11:08:03 - + [000] Schedule 2 has already been cached by job
0xD9C1E426A1AAD5498B92EDBEA79A8654
2007-05-18 11:10:01 - + [162] Internal request (from SetJobNextRunDate
[reason: schedule will not run again]) to deactivate schedule 2
2007-05-18 11:10:01 - + [163] 1 operations applied (0 remaining)
2007-05-18 11:12:09 - + [162] Internal request (from SetJobNextRunDate
[reason: schedule will not run again]) to deactivate schedule 3
2007-05-18 11:12:09 - + [000] Schedule 3 has already been cached by job
0x4CC373CEC320804D9AF67C7704B6F747
2007-05-18 11:13:23 - + [000] Schedule 4 has already been cached by job
0x6CC98A2943018F488AF45B20E63E1DC7
2007-05-18 11:15:00 - + [162] Internal request (from SetJobNextRunDate
[reason: schedule will not run again]) to deactivate schedule 4
2007-05-18 11:15:00 - + [163] 1 operations applied (0 remaining)
2007-05-18 11:22:46 - + [000] Schedule 5 has already been cached by job
0xAE9A0B33E1E4374B9B60F807A7772999
2007-05-18 11:24:00 - + [162] Internal request (from SetJobNextRunDate
[reason: schedule will not run again]) to deactivate schedule 5
2007-05-18 11:24:00 - + [163] 1 operations applied (0 remaining)
2007-05-18 11:32:02 - + [000] Schedule 6 has already been cached by job
0x05979EE4A61D87489C664CE2D68B0D24
2007-05-18 11:33:00 - + [162] Internal request (from SetJobNextRunDate
[reason: schedule will not run again]) to deactivate schedule 6
2007-05-18 11:33:00 - + [163] 1 operations applied (0 remaining)
2007-05-18 11:35:23 - + [000] Schedule 7 has already been cached by job
0xBAE7D146168A5B4586DBB7333EE1D0C5
2007-05-18 11:36:00 - + [162] Internal request (from SetJobNextRunDate
[reason: schedule will not run again]) to deactivate schedule 7
2007-05-18 11:36:00 - + [163] 1 operations applied (0 remaining)
2007-05-18 11:39:55 - + [000] Schedule 8 has already been cached by job
0x877BA9F8E7147E4EA91EB6F2E1E701C0
2007-05-18 11:41:00 - + [162] Internal request (from SetJobNextRunDate
[reason: schedule will not run again]) to deactivate schedule 8
2007-05-18 11:41:00 - + [163] 1 operations applied (0 remaining)
2007-05-18 15:40:07 - + [000] Schedule 9 has already been cached by job
0xA5BBF8F9EA07834EA0B475B553911869
2007-05-18 15:41:01 - + [162] Internal request (from SetJobNextRunDate
[reason: schedule will not run again]) to deactivate schedule 9
2007-05-18 15:41:01 - + [163] 1 operations applied (0 remaining)
2007-05-18 15:42:47 - + [000] Schedule 10 has already been cached by job
0x13322C18AB3BC5409D40385C02129F54
2007-05-18 15:45:01 - + [162] Internal request (from SetJobNextRunDate
[reason: schedule will not run again]) to deactivate schedule 10
2007-05-18 15:45:01 - + [163] 1 operations applied (0 remaining)
2007-05-18 15:48:17 - + [000] Schedule 11 has already been cached by job
0x830F8A65D9A9BC4A914123CF6CB5E8F9
2007-05-18 15:50:00 - + [162] Internal request (from SetJobNextRunDate
[reason: schedule will not run again]) to deactivate schedule 11
2007-05-18 15:50:00 - + [163] 1 operations applied (0 remaining)
2007-05-18 16:02:05 - + [000] Schedule 12 has already been cached by job
0x6E49B2772349B84CB65BD39CC3C47810
2007-05-18 16:05:01 - + [162] Internal request (from SetJobNextRunDate
[reason: schedule will not run again]) to deactivate schedule 12
2007-05-18 16:05:01 - + [163] 1 operations applied (0 remaining)
2007-05-21 09:16:02 - + [000] Schedule 13 has already been cached by job
0xEFBDC763EB628A4AA037D27A483ADE7C
2007-05-21 09:21:16 - + [000] Schedule 14 has already been cached by job
0xB79CC4672770A94492543246E78D049F
2007-05-21 09:22:40 - + [000] Schedule 15 has already been cached by job
0xA812252789CF72418FA14FEC66C90089
2007-05-21 09:31:11 - + [000] Schedule 16 has already been cached by job
0xCDDBFEFA899ACE4DBADC96FFC6932279
2007-05-21 09:33:05 - + [000] Schedule 17 has already been cached by job
0x4010169385C38E478077F9AE67D7E298I was able to correct this error message 2007-05-18 11:07:07 - ! [364] The
Messenger service has not been started -
NetSend notifications will not be sent
I started this service but it still did now process.
"OriginalStealth" wrote:
> 2007-05-18 11:07:07 - ? [100] Microsoft SQLServerAgent version 8.00.760 (x86
> unicode retail build) : Process ID
> 2007-05-18 11:07:07 - ? [100] Microsoft SQLServerAgent version 8.00.760 (x86
> unicode retail build) : Process ID 3036
> 2007-05-18 11:07:07 - ? [101] SQL Server <my computer name> version 8.00.760
> (0 connection limit)
> 2007-05-18 11:07:07 - ? [102] SQL Server ODBC driver version 3.85.1025
> 2007-05-18 11:07:07 - ? [103] NetLib being used by driver is DBMSSHRN.DLL;
> Local host server is
> 2007-05-18 11:07:07 - ? [310] 1 processor(s) and 768 MB RAM detected
> 2007-05-18 11:07:07 - ? [339] Local computer is <my computer name> running
> Windows NT 5.2 (3790)
> 2007-05-18 11:07:07 - ! [364] The Messenger service has not been started -
> NetSend notifications will not be sent
> 2007-05-18 11:07:07 - ? [129] SQLSERVERAGENT starting under Windows NT
> service control
> 2007-05-18 11:07:07 - + [260] Unable to start mail session (reason: No mail
> profile defined)
> 2007-05-18 11:07:07 - + [396] An idle CPU condition has not been defined -
> OnIdle job schedules will have no effect
> 2007-05-18 11:08:03 - + [000] Schedule 2 has already been cached by job
> 0xD9C1E426A1AAD5498B92EDBEA79A8654
> 2007-05-18 11:10:01 - + [162] Internal request (from SetJobNextRunDate
> [reason: schedule will not run again]) to deactivate schedule 2
> 2007-05-18 11:10:01 - + [163] 1 operations applied (0 remaining)
> 2007-05-18 11:12:09 - + [162] Internal request (from SetJobNextRunDate
> [reason: schedule will not run again]) to deactivate schedule 3
> 2007-05-18 11:12:09 - + [000] Schedule 3 has already been cached by job
> 0x4CC373CEC320804D9AF67C7704B6F747
> 2007-05-18 11:13:23 - + [000] Schedule 4 has already been cached by job
> 0x6CC98A2943018F488AF45B20E63E1DC7
> 2007-05-18 11:15:00 - + [162] Internal request (from SetJobNextRunDate
> [reason: schedule will not run again]) to deactivate schedule 4
> 2007-05-18 11:15:00 - + [163] 1 operations applied (0 remaining)
> 2007-05-18 11:22:46 - + [000] Schedule 5 has already been cached by job
> 0xAE9A0B33E1E4374B9B60F807A7772999
> 2007-05-18 11:24:00 - + [162] Internal request (from SetJobNextRunDate
> [reason: schedule will not run again]) to deactivate schedule 5
> 2007-05-18 11:24:00 - + [163] 1 operations applied (0 remaining)
> 2007-05-18 11:32:02 - + [000] Schedule 6 has already been cached by job
> 0x05979EE4A61D87489C664CE2D68B0D24
> 2007-05-18 11:33:00 - + [162] Internal request (from SetJobNextRunDate
> [reason: schedule will not run again]) to deactivate schedule 6
> 2007-05-18 11:33:00 - + [163] 1 operations applied (0 remaining)
> 2007-05-18 11:35:23 - + [000] Schedule 7 has already been cached by job
> 0xBAE7D146168A5B4586DBB7333EE1D0C5
> 2007-05-18 11:36:00 - + [162] Internal request (from SetJobNextRunDate
> [reason: schedule will not run again]) to deactivate schedule 7
> 2007-05-18 11:36:00 - + [163] 1 operations applied (0 remaining)
> 2007-05-18 11:39:55 - + [000] Schedule 8 has already been cached by job
> 0x877BA9F8E7147E4EA91EB6F2E1E701C0
> 2007-05-18 11:41:00 - + [162] Internal request (from SetJobNextRunDate
> [reason: schedule will not run again]) to deactivate schedule 8
> 2007-05-18 11:41:00 - + [163] 1 operations applied (0 remaining)
> 2007-05-18 15:40:07 - + [000] Schedule 9 has already been cached by job
> 0xA5BBF8F9EA07834EA0B475B553911869
> 2007-05-18 15:41:01 - + [162] Internal request (from SetJobNextRunDate
> [reason: schedule will not run again]) to deactivate schedule 9
> 2007-05-18 15:41:01 - + [163] 1 operations applied (0 remaining)
> 2007-05-18 15:42:47 - + [000] Schedule 10 has already been cached by job
> 0x13322C18AB3BC5409D40385C02129F54
> 2007-05-18 15:45:01 - + [162] Internal request (from SetJobNextRunDate
> [reason: schedule will not run again]) to deactivate schedule 10
> 2007-05-18 15:45:01 - + [163] 1 operations applied (0 remaining)
> 2007-05-18 15:48:17 - + [000] Schedule 11 has already been cached by job
> 0x830F8A65D9A9BC4A914123CF6CB5E8F9
> 2007-05-18 15:50:00 - + [162] Internal request (from SetJobNextRunDate
> [reason: schedule will not run again]) to deactivate schedule 11
> 2007-05-18 15:50:00 - + [163] 1 operations applied (0 remaining)
> 2007-05-18 16:02:05 - + [000] Schedule 12 has already been cached by job
> 0x6E49B2772349B84CB65BD39CC3C47810
> 2007-05-18 16:05:01 - + [162] Internal request (from SetJobNextRunDate
> [reason: schedule will not run again]) to deactivate schedule 12
> 2007-05-18 16:05:01 - + [163] 1 operations applied (0 remaining)
> 2007-05-21 09:16:02 - + [000] Schedule 13 has already been cached by job
> 0xEFBDC763EB628A4AA037D27A483ADE7C
> 2007-05-21 09:21:16 - + [000] Schedule 14 has already been cached by job
> 0xB79CC4672770A94492543246E78D049F
> 2007-05-21 09:22:40 - + [000] Schedule 15 has already been cached by job
> 0xA812252789CF72418FA14FEC66C90089
> 2007-05-21 09:31:11 - + [000] Schedule 16 has already been cached by job
> 0xCDDBFEFA899ACE4DBADC96FFC6932279
> 2007-05-21 09:33:05 - + [000] Schedule 17 has already been cached by job
> 0x4010169385C38E478077F9AE67D7E298