I intend to pre-process alphanumeric fields on the SQL 2005 server before reporting.

I have some strings like below:

I_CNO (field)

TR 004.
ENGLISH
REF 581
F
BIG 912
F
HOME EC
HOME EC
F ALL
F MEE
F MAL
REF 581
B MCB
ENGLISH
Student
ENGLISH
HOME EC
HOME EC
ENGLISH
F
F
F
ART 705
ART 705
HOME EC
F RAB
F
HOME EC
B ROL
B ROL
REF A80

REF 581
B HIL
REF 632
F
F
BIG 912
HOME EC
Student
REF 635
REF 635
HOME EC
F
DRAMA A
PAM 636
HOME EC
HOME EC
HOME EC
HOME EC
TEXT
B DIR
B NOR
F
BIG 919
BIG 919
BIG 919

I wish to move those prefix characters(varied in length) before the first space to another field in the same row.

First I use a select state to see how it goes:

 but result doesn't look good.

Could any SQL expert please advise how to write a suitable SQL to achieve my goal? Thanks in advance.

 

John 

 

Thank you, I made some change of your version by 'Where isnumric(left(i_cno, charindex(' ', i_cno)) = 0 ' as I intend to get A-Z only, not numeric

 

Regards,

John