none
Remove spaces in middle of a string RRS feed

  • Question

  • Hi , I have big string on one of my column and I believe when ever it starts a new line in the string its showing as space in sql table like below. Can you please how do I eliminate those spaces please see below screenshot from sql tables and actual text. Please advice

      I believes the industry has several organizations:       abcdefghijklmn - I has a disc, magic phil that combines a long-term perspective,
      That phil tested in many types of scenarios.       Advantages of Size - As solutions provider,

    Thanks

    Friday, February 8, 2019 4:23 PM

Answers

  • A simple solution is using SQL XML to replace multiple space with single space.

    You can place the code in a UDF and use it inline

    Another solution is replaces two spaces with single space until no repeating space characters are side by side in a WHILE loop

    • Edited by eralperModerator Friday, February 8, 2019 4:46 PM
    • Marked as answer by Rajm0019 Friday, February 8, 2019 4:53 PM
    Friday, February 8, 2019 4:41 PM
    Moderator
  • I just realised and updated the code.

    SELECT REPLACE(REPLACE(REPLACE(@str, CHAR(13), ''), CHAR(10), ''),'  ','')

    • Marked as answer by Rajm0019 Friday, February 8, 2019 4:52 PM
    Friday, February 8, 2019 4:43 PM

All replies

  • Replace @str with your column name.

    SELECT REPLACE(REPLACE(REPLACE(@str, CHAR(13), ''), CHAR(10), ''),'  ','')


    • Edited by Btuladhar Friday, February 8, 2019 4:41 PM
    Friday, February 8, 2019 4:38 PM
  • A simple solution is using SQL XML to replace multiple space with single space.

    You can place the code in a UDF and use it inline

    Another solution is replaces two spaces with single space until no repeating space characters are side by side in a WHILE loop

    • Edited by eralperModerator Friday, February 8, 2019 4:46 PM
    • Marked as answer by Rajm0019 Friday, February 8, 2019 4:53 PM
    Friday, February 8, 2019 4:41 PM
    Moderator
  • this replacing space between each word too. see below 

    Ibelievestheindustryhasseveralorganizations:abcdefghijklmn-Ihasadisc,magicphilthatcombinesalong-termperspective,Thatphiltestedinmanytypesofscenarios.AdvantagesofSize-Assolutionsprovider,

    • Marked as answer by Rajm0019 Friday, February 8, 2019 4:52 PM
    • Unmarked as answer by Rajm0019 Friday, February 8, 2019 4:52 PM
    Friday, February 8, 2019 4:42 PM
  • I just realised and updated the code.

    SELECT REPLACE(REPLACE(REPLACE(@str, CHAR(13), ''), CHAR(10), ''),'  ','')

    • Marked as answer by Rajm0019 Friday, February 8, 2019 4:52 PM
    Friday, February 8, 2019 4:43 PM
  • Instead of cleaning up the data once it's in the table, can you clean it up before it's put in? Remember SQL is based on a tiered architecture, so we don't do formatting in the database layer; we do in a presentation layer. Likewise, we don't do data scrubbing inside the database. We do it in the input layer.

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Saturday, February 9, 2019 10:46 PM