Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

Asked: October 31, 2018 - 3:49 pm UTC

Last updated: June 28, 2023 - 12:55 pm UTC

Version: 12.0

Viewed 10K+ times! This question is

You Asked

Haai..

i have input like this in below..

Create table Char
(
name varchar2(1000)
);

Insert all 
into Char(name) Values('Ask Tom')
into Char(name) Values('Become Again Tom')
into Char(name) Values('United Sates')
select * from dual;

Name
Ask Tom
Become Again Tom
United Sates


I need an output like below..

Name
Ask
Become
United

and Chris said...

If you want to get the first word in a string, you'll need some form of substring operation.

You can do this wtih substr + instr to find the location of the first space.

Or a regular expression to extract the letters:

with rws as (
  select 'Ask Tom' s from dual union all
  select 'Become Again Tom' s from dual union all
  select 'United Sates' s from dual 
)
  select regexp_substr ( s, '[A-z]*' ) regex,
         substr ( s, 1, instr ( s, ' ' ) - 1 ) sub
  from   rws;

REGEX    SUB      
Ask      Ask      
Become   Become   
United   United

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

regex

daddy, August 09, 2022 - 12:54 pm UTC

Regex version works only on English alphabet, no "special" characters (such as "ľšč") supported.
Chris Saxon
August 09, 2022 - 1:02 pm UTC

Well that depends on the language you're using!

But yes, in general if you need to support diacritics you should use character classes instead.

nice

A reader, November 10, 2022 - 5:07 pm UTC


Try word separator \w

bias, June 27, 2023 - 6:41 am UTC

try \w : word separator
with rws as (
select 'Ask Tom' s from dual union all
select 'Become Again Tom' s from dual union all
select 'United Sates' s from dual
)
select regexp_substr ( s, '\w*' ) regex,
substr ( s, 1, instr ( s, ' ' ) - 1 ) sub
from rws;

Chris Saxon
June 27, 2023 - 12:45 pm UTC

Thanks for sharing.

lots of possibilities with "regexp" in place.

Rajeshwaran, Jeyabal, June 28, 2023 - 2:13 am UTC

Even perhaps like this 

demo@PDB1> with rws as (
  2  select 'Ask Tom' s from dual union all
  3  select 'Become Again Tom' s from dual union all
  4  select 'United Sates' s from dual
  5  )
  6  select regexp_substr( s, '\S+' ) from rws ;

REGEXP_SUBSTR(S,'\S+')
----------------------------------------------------------------
Ask
Become
United

Chris Saxon
June 28, 2023 - 12:55 pm UTC

Yes, that works too
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.