SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Extract numbers from a string		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jpattoncook
Starting Member

USA
2 Posts
Posted - 08/31/2005 :  04:59:28  Show Profile  Reply with Quote

Objective:

I'm stripping (trying to) data from IIS weblogs and FTP log files for further analysis. I'm loading them into Excel then saving as CSV and importing into SQLServer. So far so good. (Slow process)

I've added four columns to hold the data that I need to extract from one of the weblog columns [cs-data].

[customerID]
[orderID]
[coachID]
[custName]


SQLServer version:

Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)


This is the DDL and the DML:


-- if the table exists, get rid of it
if exists
(select * from dbo.sysobjects
where id = object_id(N'[dbo].[tbl_testlog]') and
OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbl_testlog]
GO

-- Create the table
CREATE TABLE [dbo].[tbl_testlog] (

[cs_data] [varchar] (140) NOT NULL,
[customerID] [varchar] (10) NULL ,
[orderID] [varchar] (10) NULL ,
[coachID] [varchar] (10) NULL,
[custName] [varchar] (50) NULL

)
GO

INSERT INTO tbl_testlog VALUES ('http://140.99.40.16/DataAccess/Admin/ScheduleSessionDirector.asp?orderID=7477&customerID=5689&CoachID=227&CustomerName=David%20Owen',NULL, NULL, NULL, NULL)
INSERT INTO tbl_testlog VALUES ('http://140.99.40.16/DataAccess/Admin/EditCustomer.asp?CustomerID=569',NULL, NULL, NULL, NULL)
INSERT INTO tbl_testlog VALUES ('http://140.99.40.16/DataAccess/Director/notes/NoteEditor.asp?customerID=5416&orderID=7204',NULL, NULL, NULL, NULL)

GO


------------------------



Note: I use
[cs_data] [varchar] (140)
here, but in real life I use
[cs_data]varchar(1000)
just because I don't know what I'm doing. I'd like to be able to just put varchar and
have it load up to 8000 characters without me having to provide a MAX length. If you
know of a better way, I'll listen.



I've tried the examples from:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=51476

Results:

I can grab the first number but not a second number. Can't seem to grab my ass with two hands either.

5689
569
5416


Here is the select statement and what I would like in the resulting table:

SELECT [cs_data], [customerID], [orderID], [coachID] FROM [testdts].[dbo].[tbl_testlog]


cs_data customerID orderID coachID custName
-------------------------------------------------------------------------------------------------------------------------------------------- ---------- ---------- --------- --------------------------
http://140.99.40.16/DataAccess/Admin/ScheduleSessionDirector.asp?orderID=7477&customerID=5689&CoachID=227&CustomerName=David%20Owen 5689 227 NULL Owen, David
http://140.99.40.16/DataAccess/Admin/EditCustomer.asp?CustomerID=569 569 NULL NULL NULL
http://140.99.40.16/DataAccess/Director/notes/NoteEditor.asp?customerID=5416&orderID=7204 5416 7204 NULL NULL


(3 row(s) affected)

Help would be considered an act of humanity. Thank you.

madhivanan
Premature Yak Congratulator

India
22460 Posts
Posted - 08/31/2005 :  05:07:54  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Refer this also
http://weblogs.sqlteam.com/brettk/archive/2005/06/22/6328.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Kristen
Test

United Kingdom
22191 Posts
Posted - 08/31/2005 :  05:53:02  Show Profile  Reply with Quote 

DECLARE	@i1 int, @i2 int, @i3 int, @i4 int
UPDATE tbl_testlog
SET
	@i1 = charindex('CustomerID=', cs_data, 1)+11,
	customerID = CASE WHEN @i1 = 11 THEN NULL 
			ELSE substring(cs_data, @i1, charindex('&', cs_data + '&', @i1)-@i1)
			END,
	@i2 = charindex('orderID=', cs_data, 1)+8,
	orderID = CASE WHEN @i2 = 8 THEN NULL 
			ELSE substring(cs_data, @i2, charindex('&', cs_data + '&', @i2)-@i2)
			END,
	@i3 = charindex('CoachID=', cs_data)+8,
	coachID = CASE WHEN @i3 = 8 THEN NULL 
			ELSE substring(cs_data, @i3, charindex('&', cs_data + '&', @i3)-@i3)
			END,
	@i4 = charindex('CustomerName=', cs_data)+13,
	custName = REPLACE(
					CASE WHEN @i4 = 13 THEN NULL 
					ELSE substring(cs_data, @i4, charindex('&', 
						cs_data + '&', @i4)-@i4)
					END
				, '%20'
				, ' ')

Kristen
Go to Top of Page

jpattoncook
Starting Member

USA
2 Posts
Posted - 08/31/2005 :  17:38:38  Show Profile  Reply with Quote
Absoutely amazing! I'll study it and then implement. Thank you very much for your help!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000