Removing Space from string

sandeepgupta_18

Hi All ,

I was looking for a help

I have a table city

in this table there is column "City_name"

that contains value like

City_name

 

RUTLAND

Pendleton

SIERRA

RANDOLPH

YANCEY

PATRICK

DENVER

CALDWELL

SCOTLAND

KEARNEY

PASCO

CATAWBA

LINN

FORT BEND

COLLIN

RIO ARRIBA

Most of these values contains spaces  & New line character

to remove those i used upper(REGEXP_REPLACE(city_name, '[^0-9A-Za-z]', ''))

this worked for every city except where city name like "RIO ARRIBA" this removed the space between RIO and ARRIBA .

is there any way by which we could removed space, and new line character from the last and starting only if there is a space between string that should remain same..

thanks in advance

Saubhik

Use TRIM, LTRIM, RTRIM and REPLACE (if needed).

EDIT: Here are the links for you from documentation, there are examples also, so you can try yourself.

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/LTRIM.html#GUID-81B3D53C-0BBC-4485-B057-C8012CD6E40F   (LTRIM)

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/RTRIM.html#GUID-95A7DAFB-F7AB-48F4-BE24-64B3C7A840AA  (RTRIM)

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/TRIM.html#GUID-00D5C77C-19B1-4894-828F-066746235B03  (TRIM)

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/REPLACE.html#GUID-1A79BDDF-2D3B-4AD4-98E7-985B2E59DA…  (REPLACE)

Solomon Yakobson

So you want to remove trailing spaces and new line characters, right? If so, use

RTRIM("City_Name",' ' || CHR(10) || CHR(13))

SY.

Frank Kulash

Hi,

So, the sub-string returned must start and end with an alphanumeric character, is that right?

Here's one way:

REGEXP_SUBSTR ( city_name              , '[[:alnum:]]([[:alnum:] ]*[[:alnum:]])?'              )

I hope this answers you question.

If not, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the results you want from that data.

Explain, using specific examples, how you get those results from that data.

Always say what version of Oracle you're using (e.g. 12.1.0.2.0).

See the forum FAQ:

Paulzip

If you want to remove spaces, carriage returns and line feeds from the data, whether they are leading or trailing, you can use this :

LTRIM(RTRIM("City_Name", ' ' || CHR(13) || CHR(10)), ' ' || CHR(13) || CHR(10))

This will perform better than a regex solution.

sandeepgupta_18

this will not work for the  city  names those have two words  "New York" this will removed the space from these two words as well .. that should not be happn

Solomon Yakobson

sandeepgupta18 wrote:this will not work for the city names those have two words "New York" this will removed the space from these two words as well .. that should not be happn

Did you try it? It doesn't remove "in-between" spaces:

-- Added enclosing brackest to show trailing spaces and new line characters are indeed removed

SELECT  '[' || REGEXP_SUBSTR('New York   ' || CHR(10) || ' ','[[:alnum:]]([[:alnum:] ]*[[:alnum:]])?') || ']'

  FROM  DUAL

/

'['||REGEX

----------

[New York]

SQL>

But as I already stated, same can be done more efficiently using RTRIM:

-- Added enclosing brackest to show trailing spaces and new line characters are indeed removed

SELECT  '[' || RTRIM('New York   ' || CHR(10) || ' ',' ' || CHR(10) || CHR(13)) || ']'

  FROM  DUAL

/

'['||RTRIM

----------

[New York]

SQL>

SY.

Frank Kulash

Hi,

sandeepgupta18 wrote:this will not work for the city names those have two words "New York" this will removed the space from these two words as well .. that should not be happn

No, the expression in reply #3 will only remove spaces at the beginning or end or the string.  It will not remove spaces between alphanumerics.

Maybe you copied it wrong.  Post your query.

sandeepgupta_18

Sorry it was my mistake i was checking wrong values but yes it worked okay...

but can you please explain  the query and how it is working  cause i am not  good with playing regexp

SELECT  '[' || REGEXP_SUBSTR('New York   ' || CHR(10) || ' ','[[:alnum:]]([[:alnum:] ]*[[:alnum:]])?') || ']'

  FROM  DUAL

sandeepgupta_18

can you please explain  the query and how it is working  cause i am not  good with playing regexp

REGEXP_SUBSTR ( city_name

              , '[[:alnum:]]([[:alnum:] ]*[[:alnum:]])?'

              )

BrunoVroman

Hello Sandeep,

         <<can you please explain  the query and how it is working  cause i am not  good with playing regexp>>

;-)  so it takes a long time to understand today, next DBAs looking at the code tomorrow and later will also spend time on the same... And the solution with TRIM is simple to understand and short to write. What is the conclusion? Do we need the power or REGEXP? You can use a canon to kill a fly... if you want, but maybe it is not necessary?

Edited: added: and test the various options with city names like <Mantes-la-Jolie> <Paris (Texas)> <Westward Ho!> <Saint-Louis-du-Ha! Ha!>

Amazed regards,

Bruno Vroman.

P.S.: https://en.wikipedia.org/wiki/KISS_principle