replace command

can the command replace use variables ie
replace(@.Name,'.com','.com;')
im getting an error with this and i was just wondering if it was just me
JimOriginally posted by JDionne
can the command replace use variables ie

replace(@.Name,'.com','.com;')

im getting an error with this and i was just wondering if it was just me
Jim

figured it out i needed a select in my code
select replace(@.Name,'.com','.com;')
Jim

REPLACE ALL

What is the SQL equivalent to a REPLACE ALL command in VFP?
I found this in the help section:
SELECT REPLACE('abcdefghicde','cde','xxx')
GO
Is this the full syntax and where would I use this command, I tried in the
EM but it doesn't seem to work.
Preacher Man wrote:
> What is the SQL equivalent to a REPLACE ALL command in VFP?
> I found this in the help section:
> SELECT REPLACE('abcdefghicde','cde','xxx')
> GO
> Is this the full syntax and where would I use this command, I tried in the
> EM but it doesn't seem to work.
REPLACE ALL is basically similar to an UPDATE statement in SQL Server.
That is, both statements are used to update existing rows in a table.
For example:
UPDATE your_table
SET x = 123,
y = 456
WHERE z = 999 ;
You can find the full syntax in SQL Server Books Online.
David Portas
SQL Server MVP
|||> What is the SQL equivalent to a REPLACE ALL command in VFP?
IIRC Replace in VFP is used to update table records. To update rows in a SQL
table, use the UPDATE statement.

> I found this in the help section:
> SELECT REPLACE('abcdefghicde','cde','xxx')
> GO
> Is this the full syntax and where would I use this command, I tried in the
> EM but it doesn't seem to work.
You found Replace function, which is not equivalent to VFP Replace command.
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
|||Lookup REPLACE is SQL Server Books Online.
Anith
|||Ignore it :-(
Anith
|||Can the Update command be used in the EM or the QA?
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in
message news:uBF72cHEGHA.2872@.TK2MSFTNGP14.phx.gbl...
> IIRC Replace in VFP is used to update table records. To update rows in a
> SQL table, use the UPDATE statement.
>
> You found Replace function, which is not equivalent to VFP Replace
> command.
> --
> Dejan Sarka, SQL Server MVP
> Mentor
> www.SolidQualityLearning.com
>
|||"Preacher Man" <nospam> wrote in message
news:erpibxHEGHA.336@.TK2MSFTNGP14.phx.gbl...
> Can the Update command be used in the EM or the QA?
>
I recommend you use Query Analyzer. I think the only way to do it through EM
would be to create a proc containing the UPDATE statement. QA is the better
tool for SQL development.
David Portas
SQL Server MVP
|||Yes, it can. I suggest you use QA. And use transactions - i.e. commit the
changes only after you've inspected the results and they are correct.
ML
http://milambda.blogspot.com/
|||I use the REPLACE function in a UDF, to test an incoming string.
That allows me to test stuff in a SELECT with no UPDATE until after
inspection.
So, if the UDF (user defined function) is named dbo.udfGarbageRemoval, I can
use "inline" SQL to do the preliminary testing and inspection job.
For example:
SELECT udfGarbageRemoval(<fieldname>, <width>), <field> FROM <table> ORDER
BY <blah>
This gives me immediate results for inspection with no transaction required.
I simply sort ASC and DESC on the field in question, do some Q&A and test
cases, and if the function works, then I do my update as follows:
UPDATE <table>
SET <field> = udfGarbageRemoval(<field>, <width>)
This techique if VERY fast, with no transaction nor update required until
after testing.
Functions are very easy to write in SQL, and blindingly fast.
John Smith
"Preacher Man" <nospam> wrote in message
news:erpibxHEGHA.336@.TK2MSFTNGP14.phx.gbl...
> Can the Update command be used in the EM or the QA?
> "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in
> message news:uBF72cHEGHA.2872@.TK2MSFTNGP14.phx.gbl...
>

REPLACE ALL

What is the SQL equivalent to a REPLACE ALL command in VFP?
I found this in the help section:
SELECT REPLACE('abcdefghicde','cde','xxx')
GO
Is this the full syntax and where would I use this command, I tried in the
EM but it doesn't seem to work.
Preacher Man wrote:
> What is the SQL equivalent to a REPLACE ALL command in VFP?
> I found this in the help section:
> SELECT REPLACE('abcdefghicde','cde','xxx')
> GO
> Is this the full syntax and where would I use this command, I tried in the
> EM but it doesn't seem to work.
REPLACE ALL is basically similar to an UPDATE statement in SQL Server.
That is, both statements are used to update existing rows in a table.
For example:
UPDATE your_table
SET x = 123,
y = 456
WHERE z = 999 ;
You can find the full syntax in SQL Server Books Online.
David Portas
SQL Server MVP
|||> What is the SQL equivalent to a REPLACE ALL command in VFP?
IIRC Replace in VFP is used to update table records. To update rows in a SQL
table, use the UPDATE statement.

> I found this in the help section:
> SELECT REPLACE('abcdefghicde','cde','xxx')
> GO
> Is this the full syntax and where would I use this command, I tried in the
> EM but it doesn't seem to work.
You found Replace function, which is not equivalent to VFP Replace command.
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
|||Lookup REPLACE is SQL Server Books Online.
Anith
|||Ignore it :-(
Anith
|||Can the Update command be used in the EM or the QA?
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in
message news:uBF72cHEGHA.2872@.TK2MSFTNGP14.phx.gbl...
> IIRC Replace in VFP is used to update table records. To update rows in a
> SQL table, use the UPDATE statement.
>
> You found Replace function, which is not equivalent to VFP Replace
> command.
> --
> Dejan Sarka, SQL Server MVP
> Mentor
> www.SolidQualityLearning.com
>
|||"Preacher Man" <nospam> wrote in message
news:erpibxHEGHA.336@.TK2MSFTNGP14.phx.gbl...
> Can the Update command be used in the EM or the QA?
>
I recommend you use Query Analyzer. I think the only way to do it through EM
would be to create a proc containing the UPDATE statement. QA is the better
tool for SQL development.
David Portas
SQL Server MVP
|||Yes, it can. I suggest you use QA. And use transactions - i.e. commit the
changes only after you've inspected the results and they are correct.
ML
http://milambda.blogspot.com/
|||I use the REPLACE function in a UDF, to test an incoming string.
That allows me to test stuff in a SELECT with no UPDATE until after
inspection.
So, if the UDF (user defined function) is named dbo.udfGarbageRemoval, I can
use "inline" SQL to do the preliminary testing and inspection job.
For example:
SELECT udfGarbageRemoval(<fieldname>, <width>), <field> FROM <table> ORDER
BY <blah>
This gives me immediate results for inspection with no transaction required.
I simply sort ASC and DESC on the field in question, do some Q&A and test
cases, and if the function works, then I do my update as follows:
UPDATE <table>
SET <field> = udfGarbageRemoval(<field>, <width>)
This techique if VERY fast, with no transaction nor update required until
after testing.
Functions are very easy to write in SQL, and blindingly fast.
John Smith
"Preacher Man" <nospam> wrote in message
news:erpibxHEGHA.336@.TK2MSFTNGP14.phx.gbl...
> Can the Update command be used in the EM or the QA?
> "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in
> message news:uBF72cHEGHA.2872@.TK2MSFTNGP14.phx.gbl...
>
sql

REPLACE ALL

What is the SQL equivalent to a REPLACE ALL command in VFP?
I found this in the help section:
SELECT REPLACE('abcdefghicde','cde','xxx')
GO
Is this the full syntax and where would I use this command, I tried in the
EM but it doesn't seem to work.Preacher Man wrote:
> What is the SQL equivalent to a REPLACE ALL command in VFP?
> I found this in the help section:
> SELECT REPLACE('abcdefghicde','cde','xxx')
> GO
> Is this the full syntax and where would I use this command, I tried in the
> EM but it doesn't seem to work.
REPLACE ALL is basically similar to an UPDATE statement in SQL Server.
That is, both statements are used to update existing rows in a table.
For example:
UPDATE your_table
SET x = 123,
y = 456
WHERE z = 999 ;
You can find the full syntax in SQL Server Books Online.
David Portas
SQL Server MVP
--|||> What is the SQL equivalent to a REPLACE ALL command in VFP?
IIRC Replace in VFP is used to update table records. To update rows in a SQL
table, use the UPDATE statement.

> I found this in the help section:
> SELECT REPLACE('abcdefghicde','cde','xxx')
> GO
> Is this the full syntax and where would I use this command, I tried in the
> EM but it doesn't seem to work.
You found Replace function, which is not equivalent to VFP Replace command.
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com|||Lookup REPLACE is SQL Server Books Online.
Anith|||Ignore it :-(
Anith|||Can the Update command be used in the EM or the QA?
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:uBF72cHEGHA.2872@.TK2MSFTNGP14.phx.gbl...
> IIRC Replace in VFP is used to update table records. To update rows in a
> SQL table, use the UPDATE statement.
>
> You found Replace function, which is not equivalent to VFP Replace
> command.
> --
> Dejan Sarka, SQL Server MVP
> Mentor
> www.SolidQualityLearning.com
>|||"Preacher Man" <nospam> wrote in message
news:erpibxHEGHA.336@.TK2MSFTNGP14.phx.gbl...
> Can the Update command be used in the EM or the QA?
>
I recommend you use Query Analyzer. I think the only way to do it through EM
would be to create a proc containing the UPDATE statement. QA is the better
tool for SQL development.
David Portas
SQL Server MVP
--|||Yes, it can. I suggest you use QA. And use transactions - i.e. commit the
changes only after you've inspected the results and they are correct.
ML
http://milambda.blogspot.com/|||I use the REPLACE function in a UDF, to test an incoming string.
That allows me to test stuff in a SELECT with no UPDATE until after
inspection.
So, if the UDF (user defined function) is named dbo.udfGarbageRemoval, I can
use "inline" SQL to do the preliminary testing and inspection job.
For example:
SELECT udfGarbageRemoval(<fieldname>, <width> ), <field> FROM <table> ORDER
BY <blah>
This gives me immediate results for inspection with no transaction required.
I simply sort ASC and DESC on the field in question, do some Q&A and test
cases, and if the function works, then I do my update as follows:
UPDATE <table>
SET <field> = udfGarbageRemoval(<field>, <width> )
This techique if VERY fast, with no transaction nor update required until
after testing.
Functions are very easy to write in SQL, and blindingly fast.
John Smith
"Preacher Man" <nospam> wrote in message
news:erpibxHEGHA.336@.TK2MSFTNGP14.phx.gbl...
> Can the Update command be used in the EM or the QA?
> "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
> message news:uBF72cHEGHA.2872@.TK2MSFTNGP14.phx.gbl...
>

REPLACE ALL

What is the SQL equivalent to a REPLACE ALL command in VFP?
I found this in the help section:
SELECT REPLACE('abcdefghicde','cde','xxx')
GO
Is this the full syntax and where would I use this command, I tried in the
EM but it doesn't seem to work.Preacher Man wrote:
> What is the SQL equivalent to a REPLACE ALL command in VFP?
> I found this in the help section:
> SELECT REPLACE('abcdefghicde','cde','xxx')
> GO
> Is this the full syntax and where would I use this command, I tried in the
> EM but it doesn't seem to work.
REPLACE ALL is basically similar to an UPDATE statement in SQL Server.
That is, both statements are used to update existing rows in a table.
For example:
UPDATE your_table
SET x = 123,
y = 456
WHERE z = 999 ;
You can find the full syntax in SQL Server Books Online.
David Portas
SQL Server MVP
--|||> What is the SQL equivalent to a REPLACE ALL command in VFP?
IIRC Replace in VFP is used to update table records. To update rows in a SQL
table, use the UPDATE statement.

> I found this in the help section:
> SELECT REPLACE('abcdefghicde','cde','xxx')
> GO
> Is this the full syntax and where would I use this command, I tried in the
> EM but it doesn't seem to work.
You found Replace function, which is not equivalent to VFP Replace command.
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com|||Lookup REPLACE is SQL Server Books Online.
Anith|||Ignore it :-(
Anith|||Can the Update command be used in the EM or the QA?
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:uBF72cHEGHA.2872@.TK2MSFTNGP14.phx.gbl...
> IIRC Replace in VFP is used to update table records. To update rows in a
> SQL table, use the UPDATE statement.
>
> You found Replace function, which is not equivalent to VFP Replace
> command.
> --
> Dejan Sarka, SQL Server MVP
> Mentor
> www.SolidQualityLearning.com
>|||"Preacher Man" <nospam> wrote in message
news:erpibxHEGHA.336@.TK2MSFTNGP14.phx.gbl...
> Can the Update command be used in the EM or the QA?
>
I recommend you use Query Analyzer. I think the only way to do it through EM
would be to create a proc containing the UPDATE statement. QA is the better
tool for SQL development.
David Portas
SQL Server MVP
--|||Yes, it can. I suggest you use QA. And use transactions - i.e. commit the
changes only after you've inspected the results and they are correct.
ML
http://milambda.blogspot.com/|||I use the REPLACE function in a UDF, to test an incoming string.
That allows me to test stuff in a SELECT with no UPDATE until after
inspection.
So, if the UDF (user defined function) is named dbo.udfGarbageRemoval, I can
use "inline" SQL to do the preliminary testing and inspection job.
For example:
SELECT udfGarbageRemoval(<fieldname>, <width> ), <field> FROM <table> ORDER
BY <blah>
This gives me immediate results for inspection with no transaction required.
I simply sort ASC and DESC on the field in question, do some Q&A and test
cases, and if the function works, then I do my update as follows:
UPDATE <table>
SET <field> = udfGarbageRemoval(<field>, <width> )
This techique if VERY fast, with no transaction nor update required until
after testing.
Functions are very easy to write in SQL, and blindingly fast.
John Smith
"Preacher Man" <nospam> wrote in message
news:erpibxHEGHA.336@.TK2MSFTNGP14.phx.gbl...
> Can the Update command be used in the EM or the QA?
> "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
> message news:uBF72cHEGHA.2872@.TK2MSFTNGP14.phx.gbl...
>

replace

Am pretty new to SQL Server..
Am exploring the existing SPs..
I would like to know what the command replace does?
Also what does the convert do??
TIA,
Nishasee REPLACE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp) and CAST and CONVERT (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp) in the microsoft sql/server web site

rudy
http://rudy.ca/|||also, the Books OnLine shipped with SQL Server is an excelent source of information.

Half or more of all questions asked on this board could have been asnwered by reading this document first.

Replace

Hi there,

We have a field with '.' and '-' and we need that the command replace response our only numbers without spaces.

Examples:
Field: '13.880-7' -> What we need: '138807'
Field: '13880-7' -> What we need: '138807'
Field: '1.307' -> What we need: '1307'

But the server configuration return spaces using the command:
SELECT REPLACE (REPLACE ('13.880-7', '.', ''), '-', '')

RETURN:
13 880 7 (replace for spaces)

How can I solution this problem with the SET clause without change the server configuration ?!?!

Thanks

Angela & David.SELECT REPLACE (REPLACE ('13.880-7', '.',space(0)), '-',space(0))|||Originally posted by tecman
SELECT REPLACE (REPLACE ('13.880-7', '.',space(0)), '-',space(0))

Still not working... this sintax returning 'Null'.

But Thanks.

Angela & David.|||What in the server configuration causes these added spaces ?|||Originally posted by rnealejr
What in the server configuration causes these added spaces ?

I dont know... How do I to discovery this ?|||When you had referenced 'server configuration' in your 1st post - I was under the impression you knew - I am unaware of anything that would be causing this problem. Which sql server version including service pack is installed ?|||Originally posted by rnealejr
When you had referenced 'server configuration' in your 1st post - I was under the impression you knew - I am unaware of anything that would be causing this problem. Which sql server version including service pack is installed ?

Its very strange...
Because one Database is Ok, the Replace function works... cutting the string.
And in another Database (in same server) dont works....

Im going crazy.|||OK - now we have some baseline. What are the field(s), including data types, you are running replace against ?|||What is your server configuration, may U send a sp_configure? And post a sp_dboption db_name too.

Is the sql server a 2K or 7.0??

if the version is 2k, this database is slq7.0 compatible ?|||Originally posted by Asmodan
What is your server configuration, may U send a sp_configure? And post a sp_dboption db_name too.

Is the sql server a 2K or 7.0??

if the version is 2k, this database is slq7.0 compatible ?

Version: 7.0 (2k compatible)

sp_configure:

name minimum maximum config_value run_value
----------- ---- ---- ---- ----
affinity mask 0 2147483647 0 0
allow updates 0 1 0 0
cost threshold for parallelism 0 32767 5 5
cursor threshold -1 2147483647 -1 -1
default language 0 9999 0 0
default sortorder id 0 255 44 44
extended memory size (MB) 0 2147483647 0 0
fill factor (%) 0 100 0 0
index create memory (KB) 704 1600000 0 0
language in cache 3 100 3 3
language neutral full-text 0 1 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max async IO 1 255 64 64
max degree of parallelism 0 32 0 0
max server memory (MB) 4 2147483647 767 767
max text repl size (B) 0 2147483647 65536 65536
max worker threads 10 1024 255 255
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 512 512
min server memory (MB) 0 2147483647 639 639
nested triggers 0 1 1 1
network packet size (B) 512 65535 4096 4096
open objects 0 2147483647 0 0
priority boost 0 1 1 1
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 0 0
remote access 0 1 1 1
remote login timeout (s) 0 2147483647 5 5
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 0 0
resource timeout (s) 5 2147483647 10 10
scan for startup procs 0 1 0 0
set working set size 0 1 0 0
show advanced options 0 1 1 1
spin counter 1 2147483647 10000 10000
time slice (ms) 50 1000 100 100
two digit year cutoff 1753 9999 2049 2049
Unicode comparison style 0 2147483647 196611 196611
Unicode locale id 0 2147483647 1033 1033
user connections 0 32767 0 0
user options 0 4095 0 0

sp_dboption:

Settable database options:
-----------
ANSI null default
ANSI nulls
ANSI warnings
auto create statistics
auto update statistics
autoclose
autoshrink
concat null yields null
cursor close on commit
dbo use only
default to local cursor
merge publish
offline
pending upgrade
published
quoted identifier
read only
recursive triggers
select into/bulkcopy
single user
subscribed
torn page detection
trunc. log on chkpt.|||Generate sp_dboption for both tables - the 1 that removes the space and the one that doesn't.|||Yes, U must generate the sp_dboption for the databases

exec sp_dboption 'name of the db'

both, ok...|||Originally posted by rnealejr
Generate sp_dboption for both tables - the 1 that removes the space and the one that doesn't.

Look this:

Doesnt Remove: (SP_DBOPTION 'YF_CLIENT')
The following options are set:
-----------
trunc. log on chkpt.
auto create statistics
auto update statistics

Removes: (SP_DBOPTION 'SAG')
The following options are set:
-----------
auto create statistics
auto update statistics|||Is that the only difference between the 2 ? Which service pack are you running ?|||The diference dont solve the problem, may U can execute this comand, please...

SELECT REPLACE (REPLACE (REPLACE ('13.880-7', '.', ''), '-', ''), ' ', '')
GO

just copy to your query analizer and run it.|||Originally posted by Asmodan
The diference dont solve the problem, may U can execute this comand, please...

SELECT REPLACE (REPLACE (REPLACE ('13.880-7', '.', ''), '-', ''), ' ', '')
GO

just copy to your query analizer and run it.

Not yet:
13 880 7

In other server:
138807

H E L P !!!|||You are running this in query analyzer under the same sql server connection - but if you select 1 database within qa you have 1 output - select another database you receive a different output using 'SELECT REPLACE (REPLACE (REPLACE ('13.880-7', '.', ''), '-', ''), ' ', '')'|||Originally posted by rnealejr
You are running this in query analyzer under the same sql server connection - but if you select 1 database within qa you have 1 output - select another database you receive a different output using 'SELECT REPLACE (REPLACE (REPLACE ('13.880-7', '.', ''), '-', ''), ' ', '')'

This conversion its a example. Because this conversion is in a stored procedure, inside a query who bring a varchar field who need to be converted.

If I change the database, this sintax work out.

Thanks again.
(Sorry... my English is horrible)|||U are change just the database where the proc is , not the server, verify the collation, compare the option, is everything equal??

My english is bad too, where are U from??|||Originally posted by Asmodan
U are change just the database where the proc is , not the server, verify the collation, compare the option, is everything equal??

My english is bad too, where are U from??

Brazil, and U ?!?

Well... In the db_option, and sp_configure is everything equal. Where do I to see another configuration in database ?

In another server and another database is ok.|||Brasil tbem, ufa... vai ficar mais fcil... trabalha aonde?|||Originally posted by Asmodan
Brasil tbem, ufa... vai ficar mais fcil... trabalha aonde?

Heheheh !! Que engraado.
Trabalho na Sul Amrica, So Paulo... e vc ?!?!?

Ento cara, so dois DBs, um se comporta de uma forma e o outro de outra forma. Verifiquei a sp_option e sp_configure e ambas no coincidentes. Vc tem alguma sugesto ?!?|||Trabalho no lloyds, aqui na Berrini...

Trabalho mais com sybase, isto sim banco de dados....

Estamos migrando para sql 2k, e temos dois servers 7.0.

Bem, a proc que faz este convert est compilada num banco A, se compila ela num banco B, no mesmo server, ela passa a funcionar de moda diferente, vc j recompilou a proc nos dois bancos?? Pode ser algum bugzinho.
Tentei reproduzir o result errado que vc tem a, mas nem com reza brava ele coloca os espaos.
Vc pode me passar a proc?|||Originally posted by Asmodan
Trabalho no lloyds, aqui na Berrini...

Trabalho mais com sybase, isto sim banco de dados....

Estamos migrando para sql 2k, e temos dois servers 7.0.

Bem, a proc que faz este convert est compilada num banco A, se compila ela num banco B, no mesmo server, ela passa a funcionar de moda diferente, vc j recompilou a proc nos dois bancos?? Pode ser algum bugzinho.
Tentei reproduzir o result errado que vc tem a, mas nem com reza brava ele coloca os espaos.
Vc pode me passar a proc?

Mas o problema principal nem na procedure. Num simples SELECT utilizando o Replace, num Database ele corta (que o que quero) e no outro ele coloca espaos... no sei vc entendeu.

Usando:
SELECT REPLACE ('13.665-7', '.', '')

Em um ele resulta:
13665-7

No outro:
13 665-7

Eu gostaria de saber se existe algum SET XXXX {ON|OFF} que eu possa desabilitar ou habilitar algo para q funcione... E t difcil.|||Olha eu no consegui reproduzir aqui, ou seja, meio chute, mas o nico set que pode te ajudar seria

set ANSI_PADDING {on;off}|||Originally posted by Asmodan
Olha eu no consegui reproduzir aqui, ou seja, meio chute, mas o nico set que pode te ajudar seria

set ANSI_PADDING {on;off}

... ainda no funcionou.|||I can translate a little - but are you still having the problem ?|||Originally posted by rnealejr
I can translate a little - but are you still having the problem ?

Yes... i cant resolve the problem. Probably is a easy problem, but still not working.

Can U help me ?!|||Post the queries you are running under both databases ( as simple as possible ) with the replace function. At the moment include definitions of the field you are using for both databases in these replace functions.|||This may not be feasible but have you tried copying the database that is giving you the problem to see if the problem persists in the copied database ?|||RE:
Not yet:
13 880 7

In other server:
138807

...

H E L P !!!The diference dont solve the problem, may U can execute this comand, please...
SELECT REPLACE (REPLACE (REPLACE ('13.880-7', '.', ''), '-', ''), ' ', '')
just copy to your query analizer and run it.

This thread is complex to follow, however the issue is most probably quite simply the current DB compatibility setting.
For example run the following:

exec sp_dbcmptlevel
@.dbname = 'Pubs',
@.new_cmptlevel = '65'
Go
SELECT REPLACE (REPLACE (REPLACE ('13.880-7', '.', ''), '-', ''), ' ', '')
GO
exec sp_dbcmptlevel
@.dbname = 'Pubs',
@.new_cmptlevel = '80'
Go
SELECT REPLACE (REPLACE (REPLACE ('13.880-7', '.', ''), '-', ''), ' ', '')
GO|||Compatibility was already mentioned which triggered me to test it - I tried all version combinations on both sql server 7/2000 databases and no luck - the replace worked fine.

SAMI - Did this make a difference for your database ?|||RE: Compatibility was already mentioned which triggered me to test it - I tried all version combinations on both sql server 7/2000 databases and no luck - the replace worked fine.

(Please see / test the attachment to verify that it does / does not reproduce the described symptom on your systems.)

Normally, the DB cmptlevel certainly can produce the behavior described (this is fairly well known behavior, and it works as documented on all of my dev systems). When the DB cmptlevel = 60 or 65, an empty string literal ('') is interpreted as a blank (an ascii 32).

If you are saying you tried setting DB cmptlevel = 60 or 65 with all version combinations on both sql server 7/2000 databases, and then could NOT produce the following in the 65 cmptlevel DB:

SELECT REPLACE (REPLACE (REPLACE ('13.880-7', '.', ''), '-', ''), ' ', '') As '@.new_cmptlevel = ''65'''

-- Result Set:

@.new_cmptlevel = '65'
----------
13 880 7

Then your systems are interesting (what sp, hotfixes, etc. are you running)?|||We have a field with '.' and '-' and we need that the command replace response our only numbers without spaces.
Examples:
Field: '13.880-7' -> What we need: '138807'
Field: '13880-7' -> What we need: '138807'
Field: '1.307' -> What we need: '1307'
But the server configuration return spaces using the command:
SELECT REPLACE (REPLACE ('13.880-7', '.', ''), '-', '')
RETURN:
13 880 7 (replace for spaces)
Q1 How can I solution this problem with the SET clause without change the server configuration ?!?! Thanks Angela & David.

Note: As I mentioned, the issue is probably simply the current DB compatibility setting, DB cmptlevel = 60 or 65; (and not the result of a server wide setting pe se).

{I probably would have guessed as much earlier, if I had just specifically analyzed your original post's sql and result sets more fastidiously.}

A1 i
If I am in fact correct: To address this issue without changes to the DB configuration (specifically a 60 or 65 DB cmptlevel); you may create a stored procedure that takes the string input and returns a parsed result. For an example see: dba_ParseOutNonNumerics.sql

A1 ii
I do not know how you may " [solve] this problem with the SET clause ", but if there is a way to do so, I would appreciate understanding it.|||DBA - Nice recovery - I made the same mistake you did - leaving off the @.new_cmptlevel in the select statement. The pre-7 documentation reflected the issue of empty strings being interpreted as a space but the tests conflicted with the documentation.

SAMI - This was neglected to be mentioned in DBA's follow-up but you have to use both - the exec sp_dbcmptlevel and the @.new_cmptlevel in the select statement.|||I'm not sure I follow. RE:
DBA - Nice recovery - I made the same mistake you did - leaving off the @.new_cmptlevel in the select statement. The pre-7 documentation reflected the issue of empty strings being interpreted as a space but the tests conflicted with the documentation.

SAMI - This was neglected to be mentioned in DBA's follow-up but you have to use both - the exec sp_dbcmptlevel and the @.new_cmptlevel in the select statement.

Each time I execute my original post to this thread in Pubs it reproduces the symptom: (it explicitly demonstrates the use of the @.new_cmptlevel parameter of the sp_dbcmptlevel stored procedure.)

Post Subject: Much ado about the DB compatibility setting

This thread is complex to follow, however the issue is most probably quite simply the current DB compatibility setting.
For example run the following:

exec sp_dbcmptlevel
@.dbname = 'Pubs',
@.new_cmptlevel = '65'
Go
SELECT REPLACE (REPLACE (REPLACE ('13.880-7', '.', ''), '-', ''), ' ', '')
GO
exec sp_dbcmptlevel
@.dbname = 'Pubs',
@.new_cmptlevel = '80'
Go
SELECT REPLACE (REPLACE (REPLACE ('13.880-7', '.', ''), '-', ''), ' ', '')
GO

repair_allow_data_loss Repair statement not processed. Database needs to be in single user

I have to repair a database with repair_allow_data_loss level, so I start
SQL Server on single-user mode with the following command
sqlservr.exe -c -m
and run the repair command:
dbcc checkdb ('blade', repair_allow_data_loss)
I get the following error that doesn't make sense, since I am already in
single user mode:
Server: Msg 7919, Level 16, State 2, Line 1
Repair statement not processed. Database needs to be in single user mode.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Anyone can help?You don't need to start the server in single user mode for the DBCC, just
place the *database* in single-user-mode. You can do this with ALTER
DATABASE:
ALTER DATABASE MyDaabase
SET SINGLE_USER
Note that it's usually better to restore from backup than resort to DBCC
CHECKDB ... REPAIR_ALLOW_DATA_LOSS. However, the option is useful if you
have no backup.
Hope this helps.
Dan Guzman
SQL Server MVP
"savvas" <savvas@.blade.com.cy> wrote in message
news:uI0sdJFLGHA.3944@.tk2msftngp13.phx.gbl...
>I have to repair a database with repair_allow_data_loss level, so I start
> SQL Server on single-user mode with the following command
> sqlservr.exe -c -m
> and run the repair command:
> dbcc checkdb ('blade', repair_allow_data_loss)
> I get the following error that doesn't make sense, since I am already in
> single user mode:
> Server: Msg 7919, Level 16, State 2, Line 1
> Repair statement not processed. Database needs to be in single user mode.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> Anyone can help?
>