Try to check if column consists out of 3 numbers, and change the value to the first number of the co-CodePudding

I'm trying to create a new column called 'team'. In the image below you see different type of codes. The first number of the code is the team someone's in, IF the number consists out of 3 characters. E.G: 315 = team 3, 240 = team 2, and 3300 = NULL.

In the image below you can see my data flow so far and the expression I have tried, but doesn't work.

CodePudding user response：

In SQL you can try something like for SQL Server:

select id,
      code,
      case when len(code)=3  then concat('Team',' ',left(code,1)) else NULL end as team
from tbl;

Demo: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=674af50d9b36b87a5b0352e8bee2e8c5

Or MySQL

select id,
      code,
      case when length(code)=3  then concat('Team',' ',left(code,1)) else NULL end as team
from tbl;

Demo: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=af6fdff3407ffca5d4a8f761a60c0f61

Result:

id    code    team
49    315  Team 3
50    320  Team 3
51    3200 null   
52    321  Team 3
53    322  Team 3
54    330  Team 3
55    3300 null   
56    340  Team 3
57    3400 null   
58    3401 null   
59    3500 null   
60    240  Team 2

CodePudding user response：

You forget parenthesis () in your regex :

Try :

^([0-9]{3})$

Demo