Post reply

Remove spaces from an unstructured Text field

  • August 28, 2020 at 6:18 am

    #3784303

    Hi Forumers,

    I've got a table with several Notes fields and I'm trying to remove ALL spaces from the text & replace with an underscore so the text is still easy to read.

    I've used REPLACE with LTRIM & RTRIM which fixed most spaces but not all.

    I found when I pasted the text into Notepad that the text didn't just have a space but was written on the next line.

    So the text may have been copied from somewhere with CR/LF and it pasted like it was a string of text.

    Has anyone had this situation and been able to fix it?

    I need to remove any formatting to make all the text in the field run together in one continuous string.

    Thanks for any suggestions/help

  • August 28, 2020 at 6:39 am

    #3784309

    For the CR/LF you will need to do a character number replacement on a CHAR(10) CHAR(13) style

    Something like the following

    REPLACE(<My String With a CR LF>, CHAR(10)+CHAR(13),'_')

    Or maybe as its a new line you may want to do a double underscore to signify this was a CR/LF point in which case it would be something like below

    REPLACE(REPLACE(<My String With a CR LF>,CHAR(10),'_'),CHAR(13),'_').

     

    You will then need to look at other formatting, like what to do with TABS and other pieces, I would recommend getting an ASCII/CHAR sheet from the net and look at the different character numbers and do a replacement for whatever pieces you need to remove.

     

    Obviously this is not best done in the data engine as it's not designed for that, I would of pushed back and made the developers do this on the application visual side of things as then they only need to deal with the data they pull back, not every free text value you have.  SQL is not designed for text manipulation like this really so any formatting like this should be a front end piece of work.

  • August 28, 2020 at 6:42 am

    #3784310

    Perfect! Thanks Anthony.

  • August 28, 2020 at 3:29 pm

    #3784414

    I think there was a bit of a typo there, CR + LF is (13)+(10), not (10)+(13).  You probably also want to replace single CHAR(10)/CHAR(13) chars, to be safe, like so:

    REPLACE(REPLACE(REPLACE([Your String], CHAR(13)+CHAR(10), '_'), CHAR(10), '_'), CHAR(13), '_')

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • August 29, 2020 at 1:08 am

    #3784553

    Thanks Scott, I'll give it a try.

    Peter

  • September 2, 2020 at 3:01 pm

    #3785639

    Just as an aside: I like to use Notepad++ instead of the standard Notepad program for checking a text. You can instruct Notepad++ to show all control characters as symbols, which is very handy in many situations.

    Notepad++ is much, much more powerful than Notepad (and free). An indispensable tool, IMO.

     

     

  • September 2, 2020 at 10:56 pm

    #3785787

    Hi kaj,

    Totally agree, I have it installed but Notepad served the purpose this time.

    Thanks Peter

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply