How to get specific part string from column

I have two tables one is SifraArtikla

ID PuniNaziv
1  M-bus centrala tip MASTER PW60 sa adapterom
2  M-bus centrala tip MASTER PW120 sa adapterom
3  M-bus centrala tip MASTER PW250 sa adapterom

The second table ArtikalUsluge extend the SifraArtikla ID

Id ArtikalID
1  2 

I need to get from PuniNaziv column ‘PW60’ if ArtikalID is 1 else if ID is 2 ‘PW120’ else if ID is 3 ‘pw250’ I tried with if else statment but it didn’t work. Can anyone give me some hints please.

DECLARE @OznakaCentrale NCHAR
SET @OznakaCentrale = 'PW%'
IF(@OznakaCentrale == SifreArtikala.PuniNaziv)
SELECT SifreArtikala.PuniNaziv AS NazivCentrale FROM SifreArtikala
INNER JOIN ArtikliUslugeNaloga ON ArtikliUslugeNaloga.Artikal1Id = SifreArtikala.Id

This is what I want to achive

Id NazivCentrale
    
 1  PW60
 2  PW120
 3  PW250

Answer

You could use the base string functions to identify the value as being in between the text MASTER and sa. Then, join the two tables to restrict to only the ID values in the first table from whose text you want to extract.

SELECT
    sa.ID,
    SUBSTRING(sa.PuniNaziv,
              CHARINDEX('MASTER', sa.PuniNaziv) + 7,
              CHARINDEX('sa', sa.PuniNaziv) - CHARINDEX('MASTER', sa.PuniNaziv) - 8) AS P_ID
FROM SifraArtikla sa
INNER JOIN ArtikalUsluge au
    ON au.ArtikalID = sa.ID

Demo