I want to create a computed column based off a substring of another column in SQL

Question

I have a column called TAG_

Data in the TAG_ column could like the below:

STV-123456

TV-12456

ME-666666

I want to create two computed columns

One that shows the first part of TAG_ before the hyphen

STV

TV

ME

One that shows the second part of TAG_ after the hyphen

123456

12456

666666

This shouldn't be hard but the light bulb is not on yet. Please help.

Exact syntax is likely dependent of the DBMS you are using, tag your post with the one you have in mind — Lennart - Slava Ukraini, Sep 28 '22 at 13:51

Hossein Sabziani · Accepted Answer · 2022-09-28T14:09:54.673

0

try this:

SELECT SUBSTRING(TAG_ ,0,CHARINDEX('-',TAG_ ,0)) AS before,
SUBSTRING(TAG_ ,CHARINDEX('-',TAG_ ,0)+1,LEN(TAG_ )) AS after from testtable

and the result:

Hope this helps!

edited Sep 28 '22 at 14:09

answered Sep 28 '22 at 14:00

Hossein Sabziani

1
2
15
20

score 0 · Answer 2 · answered Sep 28 '22 at 14:00

Example for MySQL, syntax is likely different for other vendors:

create table t 
( tag_ text not null
, fst text generated always as (substr(tag_, 1, locate('-', tag_)-1)) stored
, snd text generated always as (substr(tag_, locate('-', tag_)+1)) stored
);

Fiddle

I want to create a computed column based off a substring of another column in SQL

2 Answers2