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.

Answer 1

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!

Answer 2

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