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