Home > mailing lists

Thread: Removing Last field from CSV string

Removing Last field from CSV string

From

Alex Magnum

Date:

16 May 2020, 15:18:57

Hi,

I have a string that I want to cut to 60 char and then remove the last field and comma.

substring('Class V,Class VI,Class VII,Competitive Exam,Class VIII,Class X,Class XI,Class IX,Class XII',1,60);

substring | Class V,Class VI,Class VII,Competitive Exam,Class VIII,Class

Now I try to remove the last field and comma ",Class"

To get Class V,Class VI,Class VII,Competitive Exam,Class VIII

Is there a function or easy way to do this?

Any help would be appreciated.

Thank you

Alex

Re: Removing Last field from CSV string

From

PALAYRET Jacques

Date:

16 May 2020, 16:31:30

Hello,

Perhaps, a statement like :

substring(theString, 1, length(theString)-position(',' IN reverse(theString)))

with theString 'Class V,Class VI,Class VII,Competitive Exam,Class VIII*,Class' for example.

Regards

----- Météo-France -----
PALAYRET JACQUES
DCSC/MBD
jacques.palayret@meteo.fr
Fixe : +33 561078319

Re: Removing Last field from CSV string

From

Adrian Klaver

Date:

16 May 2020, 16:53:17

On 5/16/20 9:31 AM, PALAYRET Jacques wrote:
> Hello,
> 
> Perhaps, a statement like :
>     substring(theString, 1, length(theString)-position(',' IN 
> reverse(theString)))
> 
> with theString   'Class V,Class VI,Class VII,Competitive Exam,Class 
> VIII*,Class' for example.

That's cool. I did a little fiddling with above:

SELECT
     substring(
     left('Class V,Class VI,Class VII,Competitive Exam,Class VIII,Class 
X,Class XI,Class IX,Class XII', 60), 1, length(
     left('Class V,Class VI,Class VII,Competitive Exam,Class VIII,Class 
X,Class XI,Class IX,Class XII', 60)) - position(',' IN reverse(
     left('Class V,Class VI,Class VII,Competitive Exam,Class VIII,Class 
X,Class XI,Class IX,Class XII', 60))))


   substring
--------------------------------------------------------
  Class V,Class VI,Class VII,Competitive Exam,Class VIII


> Regards
> ----- Météo-France -----
> PALAYRET JACQUES
> DCSC/MBD
> jacques.palayret@meteo.fr
> Fixe : +33 561078319


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: Removing Last field from CSV string

From

Michael Nolan

Date:

16 May 2020, 17:29:34

On Sat, May 16, 2020 at 10:19 AM Alex Magnum <magnum11200@gmail.com> wrote:

Hi,

I have a string that I want to cut to 60 char and then remove the last field and comma.

substring('Class V,Class VI,Class VII,Competitive Exam,Class VIII,Class X,Class XI,Class IX,Class XII',1,60);

substring | Class V,Class VI,Class VII,Competitive Exam,Class VIII,Class

Now I try to remove the last field and comma ",Class"

To get Class V,Class VI,Class VII,Competitive Exam,Class VIII

Is there a function or easy way to do this?
Any help would be appreciated.

You should be able to write a posix patter that does this, you want to keep everything except a comma followed by 0 or more non-commas and the end of the string boundary to make sure it gets just the last such match.

Mike Nolan

nolan@tssi.com

Re: Removing Last field from CSV string

From

Christian Ramseyer

Date:

16 May 2020, 19:59:24


On 16.05.20 17:18, Alex Magnum wrote:

> Now I try to remove the last  field and comma  ",Class"
> 
> To get  Class V,Class VI,Class VII,Competitive Exam,Class VIII
> 
> Is there a function or easy way to do this?
> Any help would be appreciated.
> 

Hi Alex

Many options to do this with regexp_replace, here's one way:


with test as (
    select 'Class VII,Competitive Exam,Class VIII,Class' as str
    union
    select 'Class VIIx,Competitive Exam22,Class VIIIabc,Classx'
)
select str, regexp_replace(str, '^(.*),(.*?)$', '\1') res from test;


|str
                             |res
                                                          |
|------------------------------------------------------|
|Class VII,Competitive Exam,Class VIII,Class
                             |Class VII,Competitive Exam,Class VIII

|------------------------------------------------------|
|Class VIIx,Competitive Exam22,Class VIIIabc,Classx
                             |Class VIIx,Competitive Exam22,Class
VIIIabc                                                         |


(I cut some columns at the start to better fit email width)

Cheers
Christian


-- 
Christian Ramseyer, netnea ag
Network Management. Security. OpenSource.
https://www.netnea.com

Re: Removing Last field from CSV string

From

Steve Litt

Date:

18 May 2020, 02:07:15

On Sat, 16 May 2020 23:18:57 +0800
Alex Magnum <magnum11200@gmail.com> wrote:

> Hi,
> 
> I have a string that I want to cut to 60 char and then remove the last
> field and comma.
> 
> substring('Class V,Class VI,Class VII,Competitive Exam,Class
> VIII,Class X,Class XI,Class IX,Class XII',1,60);
> 
> substring | Class V,Class VI,Class VII,Competitive Exam,Class
> VIII*,Class*
> 
> Now I try to remove the last  field and comma  ",Class"
> 
> To get  Class V,Class VI,Class VII,Competitive Exam,Class VIII
> 
> Is there a function or easy way to do this?
> Any help would be appreciated.
> 
> Thank you
> Alex

Assuming the CSV strings are in a file, my first thought would be to
get rid of the final field using AWK, and feed that into your import.
 
SteveT

Steve Litt 
May 2020 featured book: Troubleshooting Techniques
     of the Successful Technologist
http://www.troubleshooters.com/techniques

Re: Removing Last field from CSV string

From

Samuel Roseman

Date:

20 May 2020, 05:21:09

Regular expressions, in my opinion, can be a very powerful text search and replace engine if you know how to use it.
Feel free to enhance what I provided below; it seems to work for the example you provided.

postgres=# select regexp_replace(substring('Class V,Class VI,Class VII,Competitive Exam,Class VIII,Class X,Class XI,Class IX,Class XII',1,60),'(.*),\w+','\1');
                     regexp_replace                     
--------------------------------------------------------
 Class V,Class VI,Class VII,Competitive Exam,Class VIII
(1 row)




Regards,

PflugerGeek

On Saturday, May 16, 2020, 10:19:28 AM CDT, Alex Magnum <magnum11200@gmail.com> wrote:

Hi,

I have a string that I want to cut to 60 char and then remove the last field and comma.

substring('Class V,Class VI,Class VII,Competitive Exam,Class VIII,Class X,Class XI,Class IX,Class XII',1,60);

substring | Class V,Class VI,Class VII,Competitive Exam,Class VIII,Class

Now I try to remove the last field and comma ",Class"

To get Class V,Class VI,Class VII,Competitive Exam,Class VIII

Is there a function or easy way to do this?

Any help would be appreciated.

Thank you

Alex