Remove Trailing Empty Space in a Field Content

Remove trailing empty space in a field content

Are you sure the query isn't working? Try:

SELECT TOP 100 '~'+ t.notes +'~'
  FROM TABLE1 t

TOP 100 will limit the results to the first 100 rows, enough to get an idea if there's really a space in the output. If there is, and RTRIM/LTRIM is not removing it - then you aren't dealing with a whitespace character. In that case, try:

UPDATE TABLE1
  SET notes = REPLACE(notes, 
                      SUBSTRING(notes, PATINDEX('%[^a-zA-Z0-9 '''''']%', notes), 1), 
                      '')
WHERE PATINDEX('%[^a-zA-Z0-9 '''''']%', notes) <> 0

How to remove leading and trailing whitespace in a MySQL field?

You're looking for TRIM.

UPDATE FOO set FIELD2 = TRIM(FIELD2);

---

Seems like it might be worth it to mention that TRIM can support multiple types of whitespace, but only one at a time and it will use a space by default. You can, however, nest TRIMs.

 TRIM(BOTH ' ' FROM TRIM(BOTH '\n' FROM column))

If you really want to get rid of all the whitespace in one call, you're better off using REGEXP_REPLACE along with the [[:space:]] notation. Here is an example:

SELECT 
    -- using concat to show that the whitespace is actually removed.
    CONCAT(
         '+', 
         REGEXP_REPLACE(
             '    ha ppy    ', 
             -- This regexp matches 1 or more spaces at the beginning with ^[[:space:]]+
             -- And 1 or more spaces at the end with [[:space:]]+$
             -- By grouping them with `()` and splitting them with the `|`
             -- we match all of the expected values.
             '(^[[:space:]]+|[[:space:]]+$)', 

             -- Replace the above with nothing
             ''
         ), 
         '+') 
    as my_example;
-- outputs +ha ppy+

Remove Trailing Spaces and Update in Columns in SQL Server

Try
SELECT LTRIM(RTRIM('Amit Tech Corp '))

LTRIM - removes any leading spaces from left side of string

RTRIM - removes any spaces from right

Ex:

update table set CompanyName = LTRIM(RTRIM(CompanyName))

Removing trailing and leading spaces of a field using AWK or SED

This should do:

sed 's/ *| */|/g' file
||COL1|COL2|COL3|COL4|COL11|COL99|COL19|COL88|CAL9|COL84|COL98|
||500|0001|0100000000|1995|001||Test  This|00.00.0000|6,14|12,00|0,00|
||500|0001|0100000000|1995|002||Separ ated|00.00.0000|18,14|12,00|0,00|

It change any <space>|<space> with | so other space are not removed.

How can I trim leading and trailing white space?

Probably the best way is to handle the trailing white spaces when you read your data file. If you use read.csv or read.table you can set the parameterstrip.white=TRUE.

If you want to clean strings afterwards you could use one of these functions:

# Returns string without leading white space
trim.leading <- function (x)  sub("^\\s+", "", x)

# Returns string without trailing white space
trim.trailing <- function (x) sub("\\s+$", "", x)

# Returns string without leading or trailing white space
trim <- function (x) gsub("^\\s+|\\s+$", "", x)

To use one of these functions on myDummy$country:

 myDummy$country <- trim(myDummy$country)

---

To 'show' the white space you could use:

 paste(myDummy$country)

which will show you the strings surrounded by quotation marks (") making white spaces easier to spot.

How can I remove leading and trailing whitespace from all columns but one in a CSV?

Split, trim selectively, rejoin

perl -F, -lane 's/^\s+|\s+$//g for @F[0,2..$#F]; print join ",", @F' file.csv

Explanation:

Switches:

• -F/pattern/: split() pattern for -a switch (//'s are optional)
• -l: Enable line ending processing
• -a: Splits the line on space and loads them in an array @F
• -n: Creates a while(<>){...} loop for each line in your input file.
• -e: Tells perl to execute the code on command line.

Code:

• EXPR for @F[0,2..$#F]: Iterate over array slice (skipping 2nd field)
• s/^\s+|\s+$//g: Remove leading and trailing spaces from fields
• print join ",", @F: Print the results

How to remove leading and trailing white spaces from input text?

1. Using trim() method works fine, but is used in newer browsers.

    function removeWhitespaceUsingTrimMethod {
     
            var str = "    This is whitespace string for testing purpose     ";
            var wsr = str.trim();
            alert(wsr);
        }

Output:
This is whitespace string for testing purpose

From Docs:

(method) String.trim(): string

Removes the leading and trailing white space and line terminator
characters from a string.

2. Using replace() method – works in all browsers

Syntax:

testStr.replace(rgExp, replaceText);
str.replace(/^\s+|\s+$/g, '');

function removeWhitespaceUsingReplaceMethod {
         
            var str = "    This is whitespace string for testing purpose     ";
            var wsr = str.replace(/^\s+|\s+$/g, '');
            alert( wsr);
    }

Output:
This is whitespace string for testing purpose

How do I remove trailing whitespace using a regular expression?

Try just removing trailing spaces and tabs:

[ \t]+$

AWK remove leading and trailing whitespaces from fields

With your shown samples, please try following awk code. Written and tested with GNU awk, should work in any awk.

awk -F'[[:space:]]*;[[:space:]]*' -v OFS=";" '{sub(/^[[:space:]]+/,"");$1=$1} 1' Input_file

OR

awk -F'[[:blank:]]*;[[:blank:]]*' -v OFS=";" '{sub(/^[[:blank:]]+/,"");$1=$1} 1' Input_file

Explanation: Simple explanation would be, making field separator for each line as spaces(0 or more occurrences) followed by ; which is further followed by 0 or more occurrences of spaces. Setting OFS as ;. In main program, re-assigning 1st field, then simply printing line.

2nd solution: As per @jhnc nice suggestion in comments, you could try with your shown samples.

awk '{sub(/^[[:space:]]+/,"");gsub(/ *; */,";")} 1' Input_file

---

Related Topics