Solved

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

Posted on 2013-05-23
4
526 Views
Last Modified: 2013-05-23
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
Comment
Question by:carlino70
  • 2
4 Comments
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 total points
ID: 39191010
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39191015
If you are interested, here is the regex version:
regexp_substr(pointname,'^[^ ]*')
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 250 total points
ID: 39191047
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
 

Author Closing Comment

by:carlino70
ID: 39191531
It works.

Thanks to both!
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Join & Write a Comment

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now