SQL Query Help ! - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

SQL Query Help !

Author

Topic

sqldba20
Posting Yak Master

183 Posts

Posted - 2010-08-24 : 17:00:20

Folks:

I have this type of data in one of the column named 'Details' in SQL Table (SQL 2005).

CONS\abc logged to workstation LAPTOP1 at 10:45:24 AM
CONS\pqygd logged to workstation WORKSTATION1 at 10:46:29 AM
CONS\pqrst logged to workstation WORKSTATION2 at 10:47:12 AM

Of this I only need the user ids (abc, pqygd, pqrst) and the workstation names (LAPTOP1, WORKSTATION1, WORKSTATION2)...seperate this and put it in 2 columns as UserId and ComputerName.

Any help on how my SQL Statement will be?

Thanks !

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-08-24 : 19:02:02

Somebody's going to come up with a fool-proof solution. My only works if all your data is like your sample data (starts with CONS\, always has ' at ', etc.)

DECLARE @sql varchar(100)

SET @sql = -- 'CONS\abc logged to workstation LAPTOP1 at 10:45:24 AM'
'CONS\pqygd logged to workstation WORKSTATION1 at 10:46:29 AM'
--CONS\pqrst logged to workstation WORKSTATION2 at 10:47:12 AM

SELECT substring(@sql,6,charindex(' ',@sql)-6),patindex('% at %',@sql)
,substring(@sql,patindex('%workstation %',@sql)+11, patindex('% at %',@sql)-patindex('%workstation %',@sql) -11)

Jim

Everyday I learn something that somebody else already knew

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-08-24 : 19:21:53

or just as bad

,substring(@sql, patindex('%workstation %',@sql)+11, charindex(' ',reverse(substring(@sql,1,len(@sql) - 15))))

Jim

Everyday I learn something that somebody else already knew

glendcruz
Yak Posting Veteran

60 Posts

Posted - 2010-08-26 : 20:49:23

select cast(substring(@sql,charindex('\',@sql)+1,charindex(' ', @sql)-charindex('\',@sql))as varchar),
cast(substring(substring(@sql,patindex('%workstation%',@sql), patindex('%at %',@sql)-
patindex('%workstation%',@sql)),
charindex(' ',substring(@sql,patindex('%workstation%',@sql), patindex('%at %',@sql)-
patindex('%workstation%',@sql))),
len(substring(@sql,patindex('%workstation%',@sql), patindex('%at %',@sql)-
patindex('%workstation%',@sql))))as varchar)

------------------------------ ------------------------------
abc LAPTOP1

(1 row(s) affected)

glendcruz
Yak Posting Veteran

60 Posts

Posted - 2010-08-26 : 20:56:45

In the example I had writen above, I had ommitted the variables. This should suit your requirement if the format is the same as writen

DECLARE @sql varchar(100)

SET @sql = 'CONS\abc logged to workstation LAPTOP1 at 10:45:24 AM'
--'CONS\pqygd logged to workstation WORKSTATION1 at 10:46:29 AM'
--CONS\pqrst logged to workstation WORKSTATION2 at 10:47:12 AM

select cast(substring(@sql,charindex('\',@sql)+1,charindex(' ', @sql)-charindex('\',@sql))as varchar),
cast(substring(substring(@sql,patindex('%workstation%',@sql), patindex('%at %',@sql)-
patindex('%workstation%',@sql)),
charindex(' ',substring(@sql,patindex('%workstation%',@sql), patindex('%at %',@sql)-
patindex('%workstation%',@sql))),
len(substring(@sql,patindex('%workstation%',@sql), patindex('%at %',@sql)-
patindex('%workstation%',@sql))))as varchar)

------------------------------ ------------------------------
abc LAPTOP1

(1 row(s) affected)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources