guys I've done this scalar function which gives me the first and last part of the string:

for example P0_x002_3345

will give me PO3345

but for ones like this:

PO_x002e_5567_3333

I need to get PO 5567 3333

and I only get

PO3333

ALTER function [dbo].[ExtractPONumber](@Text as varchar(8000))

returns varchar(8000)

as

begin

declare @Part1 varchar(8000);

declare @Part2 varchar(8000);

if LEN(@Text) > 0

begin

while (PATINDEX('%x002%',@Text) > 1)

begin

set @Text =

replace(@Text,

'x002',

'')

end

set @Part1 = CASE WHEN CHARINDEX('_',@Text) > 0 THEN LEFT(@Text,CHARINDEX('_',@Text)-1) ELSE NULL END

--LEFT(@Text,CHARINDEX('_',@Text)-1)

set @Part2 = REPLACE(@Text,@Part1,'')

set @Part2 = REVERSE(@Part2)

set @Part2 = CASE WHEN CHARINDEX('_',@Part2) > 0 then LEFT(@Part2, CHARINDEX('_',@Part2)-1) else @Part2 END

set @Part2 = REVERSE(@Part2)

--set @Text = @Part1 + REPLACE(@Part2,'_','')

end

return @Part1 + @Part2--REPLACE(@Part1 + @Part2,'_',' ')

end