2

I have 100 columns in an Oracle table like this Sh. North Miladhunmadulu - Shaviyani Atoll . I need a query to select only Shaviyani from the above. Please help me. I need to select data after - and before Atoll word.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user3769901
  • 17
  • 1
  • 3

2 Answers2

1

Assuming you are talking about rows and not column. You can achieve this using multiple comma separated CTE.

Below query has 3 cte table. Table t0 will pull the position of the '-' using charindex function. Then using the charindex function we can find the next space which should be after the word Shaviyani. As you can see there is immediate space after '-'. Therefore we will have added +2 to t0.position to the table t1 which is used to find the position of the next space. The table t3 will get the final result using substring function. 

declare @string nvarchar(50)='Sh. North Miladhunmadulu - Shaviyani Atoll';

with t0 AS (select charindex('-',@string) pos, @string name),
     t1 AS (select charindex(' ',@string,pos+2) pos,name from t0),
     t2 AS (select substring(@string,t0.pos+1,t1.pos - t0.pos) name
            from t0
            inner join t1 on t0.name = t1.name )

select * from t2

Output

name
--------------------------------------------------
 Shaviyani
Rima
  • 1,447
  • 1
  • 6
  • 12
  • Thank you so much it is working, i have like this data in different tables. how to select based on column name from different columns. K. Male - Kaafu Atoll S. Addu - Seenu Atoll K. Male - Kaafu Atoll K. Male - Kaafu Atoll AA. North Ari - Alif Alif Atoll – user3769901 Apr 25 '19 at 06:49
  • I have using variable. you can directly mention table name. for example, with t0 AS (select charindex('-',column.name) pos, column.name from table), – Rima Apr 25 '19 at 06:54
  • How does an answer that doesn't work in Oracle get accepted, much less upvoted? – Gordon Linoff Apr 25 '19 at 11:44
1

Please use SUBSTR and INSTR as following

SELECT SUBSTR('Sh. North Miladhunmadulu - Shaviyani Atoll',instr ('Sh. North Miladhunmadulu - Shaviyani Atoll','-')+1,
        ((instr ('Sh. North Miladhunmadulu - Shaviyani Atoll','Atoll')-1) - instr ('Sh. North Miladhunmadulu - Shaviyani Atoll','-'))) as OUTPUT
    FROM dual;

Here is output at Fiddle

Replace the string by column name and dual with your table name.

Muhammad Waheed
  • 1,048
  • 1
  • 13
  • 30