当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - Grab specific string from exsiting data and put it in new column

问题描述:

I have a huge table like this:

DATA NUM CITY

----------------------------------- --------------- --------------------

ABC140180B 51368 Hong Kong

ABC140180 98563 New York

CENTER ABC140180 65125 Philadephia

ABC230142 48101 PARK

SOUTHFIELD ABC230142 48083 Tokyo

GENERAL SURGERY ABC230053LJ 45896 Philadephia

CARDIOVASCULAR MEDICINE ABC230053LK 48192 Philadephia

GYNECOLOGIC ABC230053LL 25874 Philadephia

NEUROSURGERY ABC230053LC 48192 Tokyo

HENRY ABC230053FG 11524 Tokyo

UROGYNECOLOGY ABC230053FH 62531 Tokyo

Is there any way I can Grab all string with "ABCXXXXXXX" from data and create a new table with one new column contain the data like:

DATA NUM CITY DSH

----------------------------------- --------------- -------------------- -----------------

ABC140180B 51368 Hong Kong ABC140180B

ABC140180 98563 New York ABC140180

CENTER ABC140180 65125 Philadephia ABC140180

ABC230142 48101 PARK ABC230142

SOUTHFIELD ABC230142 48083 Tokyo ABC230142

GENERAL SURGERY ABC230053LJ 45896 Philadephia ABC230053LJ

CARDIOVASCULAR MEDICINE ABC230053LK 48192 Philadephia ABC230053LK

GYNECOLOGIC ABC230053LL 25874 Philadephia ABC230053LL

NEUROSURGERY ABC230053LC 48192 Tokyo ABC230053LC

HENRY ABC230053FG 11524 Tokyo ABC230053FG

UROGYNECOLOGY ABC230053FH 62531 Tokyo ABC230053FH

Can anyone help me please

Thanks in advance

网友答案:

Based on the examples you have given, you want the last "token" in string after the last space.

Oracle provides regexp_substr():

select . . ., regexp_substr(' ' || data, ' [^ ]*$', 1, 1) as dsh
网友答案:

Can also be done with substr() and instr() :

select t.*,
       substr(t.YourColumn, instr(t.YourColumn, 'ABC') + 1)  as DSH
from YourTable t;

This will take every char from ABC and forwards

分享给朋友:
您可能感兴趣的文章:
随机阅读: