Extract numbers from a string - 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 2000 Forums

SQL Server Development (2000)

Extract numbers from a string

Author

Topic

jpattoncook
Starting Member

2 Posts

Posted - 2005-08-31 : 04:59:28

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

22864 Posts

Posted - 2005-08-31 : 05:07:54

Refer this also
http://weblogs.sqlteam.com/brettk/archive/2005/06/22/6328.aspx

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-08-31 : 05:53:02

[code]
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'
, ' ')
[/code]
Kristen

jpattoncook
Starting Member

2 Posts

Posted - 2005-08-31 : 17:38:38

Absoutely amazing! I'll study it and then implement. Thank you very much for your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources