Substring Error

Question

Post reply

Substring Error

eseosaoregie

SSCertifiable

Points: 6889
More actions
April 13, 2010 at 3:51 am

#140372

I am currently trying to format some log data inputted into a staging table. I effectively read each line of the log text file into a table column and then try and split the string. Below is an example of the text in the column which is called LineRead.
[Mon Mar 02 08:54:02 2009]Local/GMR09///Info(1013205)
[Mon Mar 02 08:54:49 2009]Local/GMR09/OTLCHK/admin/Info(1021006)
[Mon Mar 02 09:32:32 2009]Local/GMR09///Info(1013210)
For the 2nd row shown above I need to split the string into separate fields containing
Mon Mar 02 08:54:49 2009,Local,GMRO9,OLTCHK,admin. I ignore rows which contain '///'
The SQL I am using to do this is shown below
SELECT
SUBSTRING(LineRead,2,24)
,SUBSTRING(
LineRead,
CHARINDEX('/',LineRead)+1,
CHARINDEX('/',LineRead,CHARINDEX('/',LineRead)+ 1)-1 - CHARINDEX('/',LineRead))
,SUBSTRING(
LineRead,
CHARINDEX('/',LineRead,CHARINDEX('/',LineRead)+ 1)+1,
CHARINDEX('/',LineRead,CHARINDEX('/',LineRead,CHARINDEX('/',LineRead)+ 1)+1)-1
- CHARINDEX('/',LineRead,CHARINDEX('/',LineRead)+ 1))
,SUBSTRING(
LineRead,
CHARINDEX('/',LineRead,CHARINDEX('/',LineRead,CHARINDEX('/',LineRead)+ 1)+1) +1,
CHARINDEX('/',LineRead,CHARINDEX('/',LineRead,CHARINDEX('/',LineRead,CHARINDEX('/',LineRead)+ 1)+1)+1)- 1 -
CHARINDEX('/',LineRead,CHARINDEX('/',LineRead,CHARINDEX('/',LineRead)+ 1) +1))
FROM dbo.stg_LogFiles
WHERE CHARINDEX('[',LineRead) = 1
AND NOT SUBSTRING(LineRead,
CHARINDEX('/',LineRead,CHARINDEX('/',LineRead)+ 1)
,2) = '//'
AND SUBSTRING(LineRead,2,3) IN ('mon','tue','wed','thu','fri','sat','sun')
When compiling I keep getting this error:
Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.
I am not sure how to rectify this.
Any help would be much appreciated.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

karthik M One Orange Chip Points: 29139 More actions · Answer 1

why don't you use replace function?

Try this one...

Declare @STR varchar(255)

select @STR = '[Mon Mar 02 08:54:49 2009]Local/GMR09/OTLCHK/admin/Info(1021006)'

select replace(@str,'/',',')

karthik

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 2

See if this query returns any negative values

SELECT

CHARINDEX('/',LineRead,CHARINDEX('/',LineRead)+ 1)-1 - CHARINDEX('/',LineRead)

,CHARINDEX('/',LineRead,CHARINDEX('/',LineRead,CHARINDEX('/',LineRead)+ 1)+1)-1

- CHARINDEX('/',LineRead,CHARINDEX('/',LineRead)+ 1)

,CHARINDEX('/',LineRead,CHARINDEX('/',LineRead,CHARINDEX('/',LineRead,CHARINDEX('/',LineRead)+ 1)+1)+1)- 1 -

CHARINDEX('/',LineRead,CHARINDEX('/',LineRead,CHARINDEX('/',LineRead)+ 1) +1)

FROM dbo.stg_LogFiles

WHERE CHARINDEX('[',LineRead) = 1

AND NOT SUBSTRING(LineRead,

CHARINDEX('/',LineRead,CHARINDEX('/',LineRead)+ 1)

,2) = '//'

AND SUBSTRING(LineRead,2,3) IN ('mon','tue','wed','thu','fri','sat','sun')

This is actually the 3rd parameter you have passed to the 3 substring functions. These should never be negative..

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

eseosaoregie SSCertifiable Points: 6889 More actions · Answer 3

I have found some negatives and have amended the query to check this. Presumabaly I need to do this for all the parameters passed to the substring?

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 4

You will have to do this for the 2nd and 3rd parameter. But in your case the 2nd parameter cannot be negative. But you can still check to be sure..

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

eseosaoregie SSCertifiable Points: 6889 More actions · Answer 5

eseosaoregie

SSCertifiable

Points: 6889

April 13, 2010 at 4:52 am

#1150602

Many thanks. Much appreciated.