• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 553
  • Last Modified:

How to delete a space and the string after space, in a entire column ?

Helo, could you help me with the next issue?

I need delete a first space and the string after their space, in a entire column

Example with just a row:

select pointname
from from_ap
where pointname like 'AER_4L_SA6____I%';

Open in new window

say:
AER_4L_SA6____I Corriente Sal.06 33kV

Open in new window

I want:
AER_4L_SA6____I

Open in new window

It is possible to do using SUBSTR, INSTR or regular expressions, in every column 'pointname'?

Thank in advanced!
0
carlino70
Asked:
carlino70
  • 2
2 Solutions
 
slightwv (䄆 Netminder) Commented:
You can do it with regular expressions but they are expensive.

Given something this simple I would go with:
substr(pointname,1,instr(pointname,' ')-1)
0
 
slightwv (䄆 Netminder) Commented:
If you are interested, here is the regex version:
regexp_substr(pointname,'^[^ ]*')
0
 
sdstuberCommented:
if you use the substr/instr method, you have to be sure there really is a space if so, then the first post will work.

if the string might not have a space in it and you'd like to return the entire string then either use the regexp_substr method  or


case when instr(pointname,' ') = 0 then pointname else substr(pointname,1,instr(pointname,' ')-1)  end

or slightly smaller syntax using decode

decode(instr(pointname,' ') ,0, pointname, substr(pointname,1,instr(pointname,' ')-1) )
0
 
carlino70Author Commented:
It works.

Thanks to both!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now