Forum Discussion

Suvechha's avatar
Suvechha
Copper Contributor
May 03, 2024

Select the first N digits of string ?

I have data like this:
string 1: 084 - Go / No Go Training - Expanda   
string 2: 050 - Site Management  
string 3: White Card

The output I expect is

string 1: 084
string 2: 050
string 3: Null

And I want to implement it in MS SQL.

Data Warehouse
  • olafhelper's avatar
    olafhelper
    Bronze Contributor
    May 03, 2024

     

    The output I expect is

    Suvechha , you haven't described the logic for the result clearly, so I have to guess:

    ;WITH test AS
    (SELECT N'084 - Go / No Go Training - Expanda' AS String UNION ALL
     SELECT N'050 - Site Management' UNION ALL
     SELECT N'White Card')
SELECT test.String,
       CASE WHEN test.String LIKE N'[0-9][0-9][0-9]%'
            THEN LEFT(test.String, 3) 
            END AS result
FROM test
    • Suvechha's avatar
      Suvechha
      Copper Contributor
      May 03, 2024

      Hi olafhelper 
      Thanks , you query result helped but can I achieve the Final Column ?
      Field Result Final
      005 - Digup Offsider 005 5
      050 - Site Management 050 50
      005 - Digup Offsider 005 5
      005 - Digup Offsider 005 5
      Certificate III in Civil Construction NULL NULL

      • olafhelper's avatar
        olafhelper
        Bronze Contributor
        May 03, 2024

        Suvechha , convert the string part to numeric/integer

        ;WITH test AS
    (SELECT N'084 - Go / No Go Training - Expanda' AS String UNION ALL
     SELECT N'050 - Site Management' UNION ALL
     SELECT N'005 - Digup Offsider ' UNION ALL
     SELECT N'White Card')
SELECT test.String,
       CASE WHEN test.String LIKE N'[0-9][0-9][0-9]%'
            THEN LEFT(test.String, 3) 
            END AS result,
      TRY_CONVERT(int, LEFT(test.String, 3)) AS final
FROM test

Resources