REPLACE function problems with SQL Server 7.0? 
Author Message
 REPLACE function problems with SQL Server 7.0?

I'm having trouble with replace function in SQL Server 7.0 sp2 and
possibly sp3.
When I try
    select replace('Aa','a','b')
I suspect I get 'Ab' but I get
    bb
And even more, if I use binary dataypes as
    select replace(
        cast('Aa' as varbinary(2)),
        cast('a' as varbinary(1)),
        cast('b' as varbinary(1)))
I suspect get 0x4162 which is the binary representation os 'Ab' but I
also get
   bb
Is this a bug or am I missing something? Does this work right in SQL
server 2000?

Query exampe for Query Analyzer and BOL documentation for replace
follows...

I would be most thankful for your comments.

Ville
--------------------------------------------------------------------------------------------------------------------------------------

Query Example for Query Analyzer:
set nocount on
select 'I think I should get "Ab" for "select
replace(''Aa'',''a'',''b'')"'
select replace('Aa','a','b')
select 'I was wrong. I got "bb".'
select 'I think harder...'
select 'I think I should get "0x4162" for "select replace(cast(''Aa'' as
varbinary(2)),cast(''a'' as varbinary(1)),cast(''b'' as varbinary(1)))"'

select replace(cast('Aa' as varbinary(2)),cast('a' as
varbinary(1)),cast('b' as varbinary(1)))
select 'I was wrong again. I got ''bb''. This must be a bug in SQL
Server 7.0 Sp2!?'

SQL Server 7.0 SP2 answer to the query:
------------------------------------------------------------
I think I should get "Ab" for "select replace('Aa','a','b')"
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

bb
------------------------
I was wrong. I got "bb".
-----------------
I think harder...
----------------------------------------------------------------------------------------------------------------------------------

I think I should get "0x4162" for "select replace(cast('Aa' as
varbinary(2)),cast('a' as varbinary(1)),cast('b' as varbinary(1)))"
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

bb
-------------------------------------------------------------------------

I was wrong again. I got 'bb'. This must be a bug in SQL Server 7.0
Sp2!?

BOL documention for replace:

REPLACE (T-SQL)
Replaces all occurrences of the second given string expression in the
first string expression with a third expression.

Syntax
REPLACE('string_expression1', 'string_expression2',
'string_expression3')

Arguments
'string_expression1'  Is the string expression to search for
string_expression2.
string_expression1 can be of character or binary data.
'string_expression2'  Is the string expression for which to search in
string_expression1 and to replace with string_expression3.
string_expression2 can be of character or binary data.
'string_expression3'  Is the new string expression that replaces
string_expression2 in string_expression1.
string_expression3 can be of character or binary data.

Return Types
Returns character data if string_expression (1, 2, or 3) is one of the
supported character data types.
Returns binary data if string_expression (1, 2, or 3) is one of the
supported binary data types.



Wed, 16 Jun 2004 04:20:43 GMT
 REPLACE function problems with SQL Server 7.0?

This behavior is by design. You almost certainly are using a case-insensitive SQL Server 7 code page. This affects every aspect of string comparison in your system, including REPLACE function. In SQL Server 2000, you could achieve the effect you want, even in a CI installation, by using the new COLLATE statement.



  I'm having trouble with replace function in SQL Server 7.0 sp2 and possibly sp3.
  When I try
      select replace('Aa','a','b')
  I suspect I get 'Ab' but I get
      bb
  And even more, if I use binary dataypes as
      select replace(
          cast('Aa' as varbinary(2)),
          cast('a' as varbinary(1)),
          cast('b' as varbinary(1)))
  I suspect get 0x4162 which is the binary representation os 'Ab' but I also get
     bb
  Is this a bug or am I missing something? Does this work right in SQL server 2000?
  Query exampe for Query Analyzer and BOL documentation for replace follows...

  I would be most thankful for your comments.

  Ville
  --------------------------------------------------------------------------------------------------------------------------------------
  Query Example for Query Analyzer:
  set nocount on
  select 'I think I should get "Ab" for "select replace(''Aa'',''a'',''b'')"'
  select replace('Aa','a','b')
  select 'I was wrong. I got "bb".'
  select 'I think harder...'
  select 'I think I should get "0x4162" for "select replace(cast(''Aa'' as varbinary(2)),cast(''a'' as varbinary(1)),cast(''b'' as varbinary(1)))"'
  select replace(cast('Aa' as varbinary(2)),cast('a' as varbinary(1)),cast('b' as varbinary(1)))
  select 'I was wrong again. I got ''bb''. This must be a bug in SQL Server 7.0 Sp2!?'

  SQL Server 7.0 SP2 answer to the query:
  ------------------------------------------------------------
  I think I should get "Ab" for "select replace('Aa','a','b')"
  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  bb
  ------------------------
  I was wrong. I got "bb".
  -----------------
  I think harder...
  ----------------------------------------------------------------------------------------------------------------------------------
  I think I should get "0x4162" for "select replace(cast('Aa' as varbinary(2)),cast('a' as varbinary(1)),cast('b' as varbinary(1)))"
  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  bb
  -------------------------------------------------------------------------
  I was wrong again. I got 'bb'. This must be a bug in SQL Server 7.0 Sp2!?

  BOL documention for replace:

  REPLACE (T-SQL)
  Replaces all occurrences of the second given string expression in the first string expression with a third expression.

  Syntax
  REPLACE('string_expression1', 'string_expression2', 'string_expression3')

  Arguments
  'string_expression1'  Is the string expression to search for string_expression2.
  string_expression1 can be of character or binary data.
  'string_expression2'  Is the string expression for which to search in string_expression1 and to replace with string_expression3.
  string_expression2 can be of character or binary data.
  'string_expression3'  Is the new string expression that replaces string_expression2 in string_expression1.
  string_expression3 can be of character or binary data.

  Return Types
  Returns character data if string_expression (1, 2, or 3) is one of the supported character data types.
  Returns binary data if string_expression (1, 2, or 3) is one of the supported binary data types.



Wed, 16 Jun 2004 05:24:50 GMT
 REPLACE function problems with SQL Server 7.0?

Thank you buskirk

I'm using a case-insensitive SQL Server 7 code page as you mentioned.
Therefore do understand that query statement
    select replace('Aa','a','b') produces result bb

But I do not understand is that
    select replace(cast('Aa' as varbinary(2)),cast('a' as
varbinary(1)),cast('b' as varbinary(1)))
produces result
   bb

With this result I'm very confused with.
My interpretion of BOL is, that when arguments of REPLACE are binary the
result should be binary.
I find out that the result is not binary, and it is wrong when binary
data is manipulated as the following query illustrates.

Ville
------------------------------------------------------------------------------------------------------------------

This example is for Query Analyzer:





--Showing the input for replace...



select 'replacing...'
--The next 2 commented lines are not allowed. BOL says otherwise. The
result should be binary!


--This does work but not according to BOL!!!! The result is char when it
shoud be binary!


-- should be 'Ab', 0x4162

Query nayzer result is:

------- ------
Source= 0x4161
------------- ----
Bintoreplace= 0x61
--------------- ----
Bintoreplaceto= 0x62
------------
replacing...
------- ---- ------
Result= bb   0x6262

Quote:

> This behavior is by design. You almost certainly are using a
> case-insensitive SQL Server 7 code page. This affects every aspect of
> string comparison in your system, including REPLACE function. In SQL
> Server 2000, you could achieve the effect you want, even in a CI



>      replace function in SQL Server 7.0 sp2 and possibly sp3.
>      When I try
>          select replace('Aa','a','b')
>      I suspect I get 'Ab' but I get
>          bb
>      And even more, if I use binary dataypes as
>          select replace(
>              cast('Aa' as varbinary(2)),
>              cast('a' as varbinary(1)),
>              cast('b' as varbinary(1)))
>      I suspect get 0x4162 which is the binary representation os
>      'Ab' but I also get
>         bb
>      Is this a bug or am I missing something? Does this work
>      right in SQL server 2000?

>      Query exampe for Query Analyzer and BOL documentation for
>      replace follows...

>      I would be most thankful for your comments.

>      Ville
>      -----
>      --------------------------------------------------------------------------------------------------------------------------------

>      Query Example for Query Analyzer:
>      set nocount on
>      select 'I think I should get "Ab" for "select
>      replace(''Aa'',''a'',''b'')"'
>      select replace('Aa','a','b')
>      select 'I was wrong. I got "bb".'
>      select 'I think harder...'
>      select 'I think I should get "0x4162" for "select
>      replace(cast(''Aa'' as varbinary(2)),cast(''a'' as
>      varbinary(1)),cast(''b'' as varbinary(1)))"'
>      select replace(cast('Aa' as varbinary(2)),cast('a' as
>      varbinary(1)),cast('b' as varbinary(1)))
>      select 'I was wrong again. I got ''bb''. This must be a bug
>      in SQL Server 7.0 Sp2!?'

>      SQL Server 7.0 SP2 answer to the query:
>      ------------------------------------------------------------

>      I think I should get "Ab" for "select replace('Aa','a','b')"

>      ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

>      bb
>      ------------------------
>      I was wrong. I got "bb".
>      -----------------
>      I think harder...

>      ---------------------------------------------------------------------------------------------------------------------------------

>      I think I should get "0x4162" for "select replace(cast('Aa'
>      as varbinary(2)),cast('a' as varbinary(1)),cast('b' as
>      varbinary(1)))"

>      ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

>      bb

>      ------------------------------------------------------------------------

>      I was wrong again. I got 'bb'. This must be a bug in SQL
>      Server 7.0 Sp2!?

>      BOL documention for replace:

>      REPLACE (T-SQL)
>      Replaces all occurrences of the second given string
>      expression in the first string expression with a third
>      expression.

>      Syntax
>      REPLACE('string_expression1', 'string_expression2',
>      'string_expression3')

>      Arguments
>      'string_expression1'  Is the string expression to search for
>      string_expression2.
>      string_expression1 can be of character or binary data.
>      'string_expression2'  Is the string expression for which to
>      search in string_expression1 and to replace with
>      string_expression3.
>      string_expression2 can be of character or binary data.
>      'string_expression3'  Is the new string expression that
>      replaces string_expression2 in string_expression1.
>      string_expression3 can be of character or binary data.

>      Return Types
>      Returns character data if string_expression (1, 2, or 3) is
>      one of the supported character data types.
>      Returns binary data if string_expression (1, 2, or 3) is one
>      of the supported binary data types.



Wed, 16 Jun 2004 07:03:27 GMT
 REPLACE function problems with SQL Server 7.0?

Thank you buskirk

My last query example was faulty (first few lines of the query were
missing). The correct example is at the end of this message.

I'm using a case-insensitive SQL Server 7 code page as you mentioned.
Therefore do understand that query statement
    select replace('Aa','a','b') produces result bb

But I do not understand that
    select replace(cast('Aa' as varbinary(2)),cast('a' as
varbinary(1)),cast('b' as varbinary(1)))
produces result
   bb

With this result I'm very confused with.
My interpretation of BOL is, that when the arguments of REPLACE are
binary the result should be binary.
I find out that the result is not binary, and it is wrong when binary
data manipulated.

Ville
------------------------------------------------------------------------------------------------------------------

Example for Query Analyzer:
set nocount on








--Showing the input for replace...



select 'replacing...'
--The next 2 commented lines are not allowed. BOL says otherwise. The
result should be binary!


--This does work but not according to BOL!!!! The result is char when it
shoud be binary!


-- should be 'Ab', 0x4162

Query Analyzer result:
------- ------
Source= 0x4161
------------- ----
Bintoreplace= 0x61
--------------- ----
Bintoreplaceto= 0x62
------------
replacing...
------- ---- ------
Result= bb   0x6262



Wed, 16 Jun 2004 07:26:47 GMT
 REPLACE function problems with SQL Server 7.0?
Looks like a bug to me   ;-(

I'll pass this on to Microsoft. If you want feedback as to Microsoft's
response, please email me privately.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:
> Thank you buskirk

> My last query example was faulty (first few lines of the query were
> missing). The correct example is at the end of this message.

> I'm using a case-insensitive SQL Server 7 code page as you mentioned.
> Therefore do understand that query statement
>     select replace('Aa','a','b') produces result bb

> But I do not understand that
>     select replace(cast('Aa' as varbinary(2)),cast('a' as
> varbinary(1)),cast('b' as varbinary(1)))
> produces result
>    bb

> With this result I'm very confused with.
> My interpretation of BOL is, that when the arguments of REPLACE are
> binary the result should be binary.
> I find out that the result is not binary, and it is wrong when binary
> data manipulated.

> Ville
> --------------------------------------------------------------------------

----------------------------------------

- Show quoted text -

Quote:

> Example for Query Analyzer:
> set nocount on








> --Showing the input for replace...



> select 'replacing...'
> --The next 2 commented lines are not allowed. BOL says otherwise. The
> result should be binary!


> --This does work but not according to BOL!!!! The result is char when it
> shoud be binary!


> -- should be 'Ab', 0x4162

> Query Analyzer result:
> ------- ------
> Source= 0x4161
> ------------- ----
> Bintoreplace= 0x61
> --------------- ----
> Bintoreplaceto= 0x62
> ------------
> replacing...
> ------- ---- ------
> Result= bb   0x6262



Wed, 16 Jun 2004 08:35:53 GMT
 REPLACE function problems with SQL Server 7.0?
BP and Ville,

  There aren't many details in this KB article, but it appears to be a known
bug:

[BUG: Some String Functions Do Not Work as Expected on Binary Data (Q276195)]

Steve Kass
Drew University

Quote:

> Looks like a bug to me   ;-(

> I'll pass this on to Microsoft. If you want feedback as to Microsoft's
> response, please email me privately.

> -------------------------------------------
> BP Margolin
> Please reply only to the newsgroups.
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> can be cut and pasted into Query Analyzer is appreciated.



> > Thank you buskirk

> > My last query example was faulty (first few lines of the query were
> > missing). The correct example is at the end of this message.

> > I'm using a case-insensitive SQL Server 7 code page as you mentioned.
> > Therefore do understand that query statement
> >     select replace('Aa','a','b') produces result bb

> > But I do not understand that
> >     select replace(cast('Aa' as varbinary(2)),cast('a' as
> > varbinary(1)),cast('b' as varbinary(1)))
> > produces result
> >    bb

> > With this result I'm very confused with.
> > My interpretation of BOL is, that when the arguments of REPLACE are
> > binary the result should be binary.
> > I find out that the result is not binary, and it is wrong when binary
> > data manipulated.

> > Ville
> > --------------------------------------------------------------------------
> ----------------------------------------

> > Example for Query Analyzer:
> > set nocount on








> > --Showing the input for replace...



> > select 'replacing...'
> > --The next 2 commented lines are not allowed. BOL says otherwise. The
> > result should be binary!


> > --This does work but not according to BOL!!!! The result is char when it
> > shoud be binary!


> > -- should be 'Ab', 0x4162

> > Query Analyzer result:
> > ------- ------
> > Source= 0x4161
> > ------------- ----
> > Bintoreplace= 0x61
> > --------------- ----
> > Bintoreplaceto= 0x62
> > ------------
> > replacing...
> > ------- ---- ------
> > Result= bb   0x6262



Fri, 18 Jun 2004 05:00:30 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. Problem creating a user defined function in SQL Server 7.0

2. Access 2000: SQL problem (Replace function)

3. Replacing hardware on WIN2K Adv Servers w/ SQL 7.0 E

4. Remove/Replace special characters in SQL Server 7.0 text fields

5. SQL Server 7.0 on NT Server to SQL Server 7.0 on win2k Pro

6. SQL Server 7.0 on NT Server to SQL Server 7.0 on win2k Pro

7. SQL Server 7.0 on NT Server to SQL Server 7.0 on win2k Pro

8. SQL Server 7.0 on NT Server to SQL Server 7.0 on win2k Pro

9. SQL 7.0 Random function, does not work like SQL 6.5 Random function

10. Performance problem: SQL Server 7.0 vs SQL Server 2000

11. SQL Server 6.5 > SQL Server 7.0 problems

12. Problem: SQL Server 7.0/Microsoft Query 8.0/SQL Server ODBC driver on MacOS 9


 
Powered by phpBB® Forum Software