Finding the numeric values in a field and put the results into a column called Orig_PB_Code within the same table

1

Hi Guys,

I am new to SQL so please bear with me,

I have a table called PB_Code_Date which contains the following fields amongst others;

PB_Code Orig_PB_Code

P12345 Null
FDP P2321234 Null
P98763 P987999
P999921 P999999

Is it possible to create a stored procedure which can find the "P" preceding the numerical numbers in the column PB_Code and put that value into a column called Orig_PB_Code but only if the column Orig_PB_Code has a value of null?

so the result would look like

PB_Code Orig_PB_Code

P12345 P12345
FDP P2321234 P2321234
P98763 P987999
P999921 P999999

Many thanks in advanced.

Roy

2 
UPDATE PB_Code_Date SET
	Orig_PB_Code = STUFF(PB_Code,1,PATINDEX('%P[0-9]%',PB_Code)-1,'')
WHERE
	Orig_PB_Code IS NULL;
1 Like
3

Thanks JamesK, that works brill, I will add this to a Stored Proc, and job done. :slight_smile: