Replace Alphabet.. (1 Viewer)

[raghuprabhu]

Hi Everyone...

Attached is a small database with one table with two columns as shown below.

Service Service No.
1 F327445
1 421547
1 8014432
2 O22771
2 L185244
2 O852424
3 O321322
3 R552552
3 O8471254

Basically I want to do the following

If service is 1 remove the alphabet from the first place in service number

If service is 2 remove the what ever the alphabet and replace it with A unless the numerical parts starts with 8 then replace with O

If service is 2 remove the what ever the alphabet and replace it with N unless the numerical parts starts with 8 then replace with O

I want the outlook as follows....

Service Service No.
1 327445
1 421547
1 8014432
2 A22771
2 A185244
2 O852424
3 N321322
3 N552552
3 O8471254

thank you..

 

[jdraw]

These statements seem to conflict???

If service is 2 remove the what ever the alphabet and
 replace it with[B] A[/B] unless the numerical parts starts with 8 then replace with O

If service is 2 remove the what ever the alphabet and
 replace it with [B]N[/B] unless the numerical parts starts with 8 then replace with O

 

[raghuprabhu]

Ops

If service is 3 remove the what ever the alphabet and
replace it with N unless the numerical parts starts with 8 then replace with O