Page 1 of 3 123 LastLast
Results 1 to 15 of 36

Thread: Replace

  1. #1
    Join Date
    Dec 2002
    Posts
    15

    Unanswered: 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 ?!?!


    Thankīs


    Angela & David.

  2. #2
    Join Date
    Nov 2002
    Posts
    24

    Post Try this

    SELECT REPLACE (REPLACE ('13.880-7', '.',space(0)), '-',space(0))
    TECMAN

  3. #3
    Join Date
    Dec 2002
    Posts
    15

    Re: Try this

    Originally posted by tecman
    SELECT REPLACE (REPLACE ('13.880-7', '.',space(0)), '-',space(0))
    Still not working... this sintax returning 'Null'.

    But Thankīs.

    Angela & David.

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    What in the server configuration causes these added spaces ?

  5. #5
    Join Date
    Dec 2002
    Posts
    15
    Originally posted by rnealejr
    What in the server configuration causes these added spaces ?
    I donīt know... How do I to discovery this ?

  6. #6
    Join Date
    Feb 2002
    Posts
    2,232
    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 ?

  7. #7
    Join Date
    Dec 2002
    Posts
    15
    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 ?
    Itīs very strange...
    Because one Database is Ok, the Replace function works.... cutting the string.
    And in another Database (in same server) donīt works.....

    Iīm going crazy.

  8. #8
    Join Date
    Feb 2002
    Posts
    2,232
    OK - now we have some baseline. What are the field(s), including data types, you are running replace against ?

  9. #9
    Join Date
    Dec 2002
    Posts
    9

    Wink

    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 ???

  10. #10
    Join Date
    Dec 2002
    Posts
    15
    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.

  11. #11
    Join Date
    Feb 2002
    Posts
    2,232
    Generate sp_dboption for both tables - the 1 that removes the space and the one that doesn't.

  12. #12
    Join Date
    Dec 2002
    Posts
    9
    Yes, U must generate the sp_dboption for the databases


    exec sp_dboption 'name of the db'

    both, ok....

  13. #13
    Join Date
    Dec 2002
    Posts
    15
    Originally posted by rnealejr
    Generate sp_dboption for both tables - the 1 that removes the space and the one that doesn't.
    Look this:

    Doesnīt 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

  14. #14
    Join Date
    Feb 2002
    Posts
    2,232
    Is that the only difference between the 2 ? Which service pack are you running ?

  15. #15
    Join Date
    Dec 2002
    Posts
    9
    The diference donīt 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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •