Extract LoginID from a string....

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Extract LoginID from a string....

Author

Topic

sachingovekar
Posting Yak Master

101 Posts

Posted - 2009-01-23 : 07:09:29

Hi,

I have data in the following format in one table

Col1 Col2 LOGINID

this isGH75684 NULL NULL

DFGERTYE99978 DFDSGFSD678 NULL

NULL which isRT56789 NULL

LOGISTINCTS IN I knowsGT34562 NULL

AFTER EXECUTION SHOULD LOOK LIKE

Col1 Col2 LOGINID

this isGH75684 NULL GH75684

DFGERTYE99978 DFDSGFSD678 YE99978

NULL which isRT56789 RT56789

LOGISTINCTS IN I knowsGT34562 GT34562

I want to populate LoginID column by using Col1 and Col2
The Login ID is placed randomly in the text.

Login ID is in the format AB12345 (first two character and then five integers)

The script should first search login ID in Col1, If not found in Col1 the search in Col2, IF FOUND UPDATE LOGINID column

Please help.

Regards,
Sachin

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-23 : 09:48:33

something like


UPDATE table
SET LoginID=COALESCE(SUBSTRING(col1,PATINDEX('%[0-9]%',Col1)-2,LEN(col1)),SUBSTRING(col2,PATINDEX('%[0-9]%',Col2)-2,LEN(col2)))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources