By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH SifraArtikla AS (
SELECT 1 AS ID, 'M-bus centrala tip MASTER PW60 sa adapterom' AS PuniNaziv UNION ALL
SELECT 2, 'M-bus centrala tip MASTER PW120 sa adapterom' UNION ALL
SELECT 3, 'M-bus centrala tip MASTER PW250 sa adapterom'
),
ArtikalUsluge AS (
SELECT 1 AS Id, 2 AS ArtikalID
)
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
| ID | P_ID |
|---|---|
| 2 | PW120 |