Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2004
    Posts
    4

    Question Unanswered: Error In Replace With Spaces

    Hi !
    I'm trying to put a space after . and , in varchar fields.

    I've tried

    UPDATE Resultados
    SET RespuestaAbierta = REPLACE(RespuestaAbierta, ',', ', ')

    but nothing happened

    What's wrong ??

    Thanks and sorry if my english isn´t so good.

    Eduardo (from Argentina)

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    This works fine for me:

    drop table test2
    create table test2(f1 varchar(1000))
    insert test2 select '1,2,3,4,5,6,7'
    select REPLACE(f1, ',', ', ') from test2

    update test2 set f1=REPLACE(f1, ',', ', ')
    select * from test2

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    It seems to work fine for me, using:
    Code:
    SELECT Replace('1,2,3,4,5,6,7,8,9', ',', ', ')
    Maybe the character you are looking for only looks like it is a comma.

    -PatP

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Trabajos para mí

    Code:
    DECLARE @RespuestaAbierta varchar(50)
    SELECT @RespuestaAbierta = 'Brett,Kaiser'
    SELECT REPLACE(@RespuestaAbierta, ',', ', ')
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jun 2004
    Posts
    4
    I can't understand,

    this works fine ....

    drop table test2
    create table test2(f1 varchar(4000))
    insert test2 select '1,2,3,4,5,6,7'
    insert test2 select 'ninguna en especial,me la recomendo mi hermano.'
    select REPLACE(f1, ',', ', ') from test2
    update test2 set f1=REPLACE(f1, ',', ', ')

    select * from test2
    1, 2, 3, 4, 5, 6, 7
    ninguna en especial, me la recomendo mi hermano.


    but this one doesn't

    select respuestaabierta,
    REPLACE(respuestaabierta, ',', ', ')
    from resultados
    where idresultado = 1244591

    ninguna en especial,me la recomendo mi hermano. ninguna en especial,me la recomendo mi hermano.

    The field respuestaabierta is varchar(4000) null

    Thanks for your answers !

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I remeber...the answer is that the comma in your column is not a comma!

    That bugged us for a while once....

    select char(130), ascii(',')


    Do This

    Code:
    
    DECLARE @x varchar(80)
    SELECT @x = 'This is not a comma' + CHAR(130) + ' even though it looks like one'
    SELECT @x
    -- Then do
    
    select CHARINDEX(CHAR(130),respuestaabierta) 
    from resultados
    where idresultado = 1244591
    
    -- If it's not 0, then that's what it is....
    And let us know....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jun 2004
    Posts
    4
    Thanks, Brett

    but that's not the problem :

    select CHARINDEX(CHAR(130),respuestaabierta) , CHARINDEX(CHAR(44),respuestaabierta)
    from resultados
    where idresultado = 1244591
    Output:

    0 20

    I've noticed that the table has

    Collation = SQL_Latin1_General_CP1_CI_AS

    and, with Collation = databasedefault works fine

    what does it mean ?

    Thanks

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Damn...I thought that might have been it...

    Can you post the DDL of your table?

    Do you know how to do that in Enterprise manager?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Jun 2004
    Posts
    4

    Smile I gave up

    Bred,
    thanks for everything, I gave up
    I did a stored procedure wich updates row by row (instead a single update command).
    Thanks again. Eduardo (from Argentina)

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Too bad....I was gonna suggest that you find the position and say

    SELECT ASCII(SUBSTRING(col1,n,1))

    to see what the damn thing was...

    what code did you use?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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