Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2013-05-23
4
Medium Priority
?
539 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1000 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 77

Expert Comment

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

Accepted Solution

by:
sdstuber earned 1000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

670 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