Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Oct 2003
    Posts
    4

    Unanswered: Removing spaces in Varchar 2000 field

    I have a data field defined as Varchar 2000.
    I need to remove all spaces greater than 2 in length from the inside of the data. I can remove beginning and ending spaces.

    Data Example:
    "AAAAAAAAAAAA BBBBB CCCCCC DDDD EEEEEEE"

    Need this:
    "AAAAAAAAAAAA BBBBB CCCCCC DDDD EEEEEEE"

    Can anyone help?

  2. #2
    Join Date
    Oct 2003
    Posts
    4

    More Info

    The data didn't print like I typed it. Need to remove all spaces in middle of data that are equal to 2 in a row or more.

  3. #3
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Re: More Info

    Originally posted by mikeinoxford
    The data didn't print like I typed it. Need to remove all spaces in middle of data that are equal to 2 in a row or more.
    Let say 1=space

    select replace('qw11er11tt','11 ','1')

  4. #4
    Join Date
    Oct 2003
    Posts
    4

    Thanks

    I will test that. I'm currently working on a process using 'REPLACE'

  5. #5
    Join Date
    Oct 2003
    Posts
    4

    Thanks Again

    One problem is that I don't know the number of spaces. The number of spaces could be 2 or 50. Working on process to 'clean' out all concessive spaces with length of 2 or more.

  6. #6
    Join Date
    Feb 2004
    Location
    Poland
    Posts
    96
    I have the same problem

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    And the key here is that you want to retain 1 space between each "word".

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

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This probably isn't perfect, but it works. Note that the last (2) replace occurs twice, that is important.
    Code:
    DECLARE @c1	VARCHAR(20)
    ,  @c2		VARCHAR(20)
    ,  @c4		VARCHAR(20)
    ,  @c8		VARCHAR(20)
    ,  @c16		VARCHAR(20)
    ,  @cDemo	VARCHAR(128)
    
    SELECT @c1 = Space(1)
    ,  @c2 = Space(2)
    ,  @c4 = Space(4)
    ,  @c8 = Space(8)
    ,  @c16 = Space(16)
    ,  @cDemo = 'This is  a   test    of     the      ability       to'
    +  '        remove         spaces.'
    
    SELECT Replace(Replace(Replace(Replace(Replace(@cDemo, @c16, @c1)
    ,  @c8, @c1), @c4, @c1), @c2, @c1), @c2, @c1)
    -PatP

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I'm stuck...

    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    CREATE TABLE myTable99(Col1 varchar(8000))
    GO
    
    INSERT INTO myTable99(Col1)
    SELECT 'A quick Brown Fox jumps over the lazy dog' UNION ALL
    SELECT 'A  quick   Brown     Fox      jumps       over        the         lazy          dog'
    GO
    
    SELECT REPLACE(REPLACE(Col1,SPACE(2),'@'),'@ ',' ') AS Col1 FROM myTable99
    GO
    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.

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    And one more

    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    CREATE TABLE myTable99(Col1 varchar(8000))
    GO
    
    INSERT INTO myTable99(Col1)
    SELECT 'A quick Brown Fox jumps over the lazy dog' UNION ALL
    SELECT 'A  quick   Brown     Fox      jumps       over        the         lazy' + SPACE (2145) + 'dog'
    GO
    
    DECLARE @c1	VARCHAR(20)
    ,  @c2		VARCHAR(20)
    ,  @c4		VARCHAR(20)
    ,  @c8		VARCHAR(20)
    ,  @c16		VARCHAR(20)
    ,  @c32		VARCHAR(20)
    ,  @c64		VARCHAR(20)
    ,  @c128	VARCHAR(20)
    
    SELECT @c1 = Space(1)
    ,  @c2 = Space(2)
    ,  @c4 = Space(4)
    ,  @c8 = Space(8)
    ,  @c16 = Space(16)
    
    SELECT Replace(Replace(Replace(Replace(Replace(Replace(Col1, @c16, @c1)
    ,  @c8, @c1), @c4, @c1), @c2, @c1), @c2, @c1), @c2, @c1) FROM myTable99
    GO
    There's gotta be a better way...
    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.

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Brett Kaiser
    There's gotta be a better way...
    I don't know of any better way using pure Transact-SQL. While it is ugly, this really is pretty fast and not obnoxiously difficult to code.

    -PatP

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by mikeinoxford
    One problem is that I don't know the number of spaces. The number of spaces could be 2 or 50. Working on process to 'clean' out all concessive spaces with length of 2 or more.
    So if there are three consecutive spaces, do you want them ALL removed, or did you want to leave one space as a separator?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...and is this a one type deal or part of a repeatable process? Are you looking at actually modifying the underlying data, or just removing the spaces for display. 'Cause its not clear here even what type of code is appropriate; View, Stored Procedure, User-defined function?

    Here is one method that cleans all but one intervening space from the data in the table:

    Code:
    SET NOCOUNT ON
    CREATE TABLE #myTable99(Col1 varchar(8000))
    GO
    
    INSERT INTO #myTable99(Col1)
    SELECT 'A quick Brown Fox jumps over the lazy dog'
    UNION ALL
    SELECT 'A  quick   Brown     Fox      jumps       over        the         lazy' + SPACE (2145) + 'dog'
    
    update	#myTable99
    set	Col1 = replace(Col1, '  ', ' ')
    where	charindex('  ', Col1) > 0
    
    while	@@RowCount > 0
    	update	#myTable99
    	set	Col1 = replace(Col1, '  ', ' ')
    	where	charindex('  ', Col1) > 0
    
    select * from #myTable99
    
    drop table #myTable99
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Feb 2004
    Location
    Poland
    Posts
    96
    I steel have problem

    I have table with nvarchar and numbers in it.
    It's imposible to convert it to int so I was thinking about spaces behand the number bat the resoliution you showed dosn't works in this case.

    So I'm thinking about function (maybe something els) which will take only my numbers which i can convert to int.


    Do you have any idea

  15. #15
    Join Date
    Feb 2004
    Location
    Poland
    Posts
    96
    I import the table from XLS. and in XLS it looks like space behind the number.

Posting Permissions

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