| Author |
 Topic  |
|
|
xhostx
Constraint Violating Yak Guru
USA
277 Posts |
 Posted - 06/18/2012 : 15:45:54
|
Hi everyone,
what i'm trying to do here is to find the position of either space or horizontal tab and extract my data from the index of the first position till the next position found. here is an example:
input from a column of table
column1
1.100.145.100 SDCFTSWUT00 - FLIP FACTORY
12.8.132.141 GHNJKFPIREW
112.198.2.200 ABCDSXWERT # Splicer Network
output to 2 different columns of a new table
column1 column2
1.100.145.100 SDCFTSWUT00
12.8.132.141 GHNJKFPIREW
112.198.2.200 ABCDSXWERT.
This is what I have as script:
DECLARE IP_cursor CURSOR
FOR
SELECT value1 FROM import
OPEN IP_cursor
GO
DECLARE @i int, @ip varchar(20), @server varchar(32), @line varchar(255), @s1 int, @s2 int,@machine varchar(32)
FETCH NEXT FROM IP_cursor INTO @line
while @@FETCH_STATUS = 0
begin
select @i = 1, @ip = '', @server = '', @s1=0, @s2=1000, @machine=''
while @i < datalength(@line) +1
begin
print @i
if substring(@line,@i,1) =char(32) or substring(@line,@i,1) =char(9)
begin
select @server = RTRIM(LTRIM(substring(@line,@i+1,datalength(@line))))
select @s1=charindex(char(32),@server,@i)
select @s2=charindex(char(9),@server,@i)
print @server
print charindex(char(32),@server,@i)
print '-'
print charindex(char(9),@server,@i)
-- Case 1
if @s1 = @s2
begin
select @machine=@server
break
end
-- Case 2
if @s1 < @s2
begin
select @machine=substring(@server,1,@s1)
break
end
-- Case 3
if @s2 < @s1
begin
select @machine=substring(@server,1,@s2)
break
end
end
else
begin
select @ip = @ip + substring(@line,@i,1)
end
select @i=@i+1
end
insert audit(ipaddress,machine) values(@ip,@machine)
FETCH NEXT FROM IP_cursor INTO @line
end
Go
close IP_cursor
deallocate IP_cursor
go
My problem is that the charindex function returns 0 sometimes even if I have spaces or tabs in the string.
This is under SQL 2000.
Any help will b appreciated.
Thanks,
kml |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
52326 Posts |
Posted - 06/18/2012 : 23:08:25
|
SELECT RTRIM(LEFT(column1,PATINDEX('%[A-Za-z]%',column1)-1)) AS [IP],
STUFF(LEFT(column1,CHARINDEX('-',column1)-1),1,PATINDEX('%[A-Za-z]%',column1)-1,'') AS [StringPart]
FROM table
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
xhostx
Constraint Violating Yak Guru
USA
277 Posts |
Posted - 06/19/2012 : 08:12:39
|
quote:
Originally posted by visakh16
SELECT RTRIM(LEFT(column1,PATINDEX('%[A-Za-z]%',column1)-1)) AS [IP],
STUFF(LEFT(column1,CHARINDEX('-',column1)-1),1,PATINDEX('%[A-Za-z]%',column1)-1,'') AS [StringPart]
FROM table
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Hi visakhm,
Thanks for your help.
I tried this select but it shows the following error!!
Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.
Any idea why please?
kml |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
52326 Posts |
Posted - 06/19/2012 : 14:16:09
|
reason is because your data format is not consistent as i doubted. try this variant too. if not working post what all formats you've data in your column
SELECT RTRIM(LEFT(column1,PATINDEX('%[A-Za-z]%',column1 + 'Z')-1)) AS [IP],
STUFF(LEFT(column1,CHARINDEX('-',column1 + '-')-1),1,PATINDEX('%[A-Za-z]%',column1 + 'Z')-1,'') AS [StringPart]
FROM table
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
xhostx
Constraint Violating Yak Guru
USA
277 Posts |
Posted - 06/19/2012 : 15:04:11
|
Hi,
Thanks again for this try, it partially worked because the data doesnt have one unique format
e.g:
1.100.145.100 SDCFTSWUT00 - FLIP FACTORY
12.8.132.141 GHNJKFPIREW
112.198.2.200 ABCDSXWERT # Splicer Network
It looks like the white spaces are ALL spaces char(32), but when I checked the spaces between the IP, machine name and the comment, it look like some times there horizontal tab char(9).
I'm thinking to check on the first occurrence of either char(32) or char(9), compare the positions and then extract.
However, it seems to be hard to do in the select.
Any help PLEASE.
Thanks
kml |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
52326 Posts |
Posted - 06/19/2012 : 15:08:39
|
then would suggest you do it as batches for each of different formats
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2875 Posts |
Posted - 06/19/2012 : 15:23:50
|
This may cause bleeding gums and other serious side effects
select SUBSTRING(@str,1,PATINDEX('%[A-Z]%',@str)-2)
,SUBSTRING(@str
,PATINDEX('%[A-Z]%',@str)
,PATINDEX('%['+CHAR(9)+''+char(32)+']%',SUBSTRING(@str,1,PATINDEX('%[A-Z]%',@str)))-2)
Jim
Everyday I learn something that somebody else already knew |
|
|
|
xhostx
Constraint Violating Yak Guru
USA
277 Posts |
Posted - 06/20/2012 : 08:05:34
|
quote:
Originally posted by visakh16
then would suggest you do it as batches for each of different formats
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Hi visak,
What did you please mean with batches, would that be cursors or while loops?
Thanks,
kml |
|
|
|
xhostx
Constraint Violating Yak Guru
USA
277 Posts |
Posted - 06/20/2012 : 08:12:26
|
quote:
Originally posted by jimf
This may cause bleeding gums and other serious side effects
select SUBSTRING(@str,1,PATINDEX('%[A-Z]%',@str)-2)
,SUBSTRING(@str
,PATINDEX('%[A-Z]%',@str)
,PATINDEX('%['+CHAR(9)+''+char(32)+']%',SUBSTRING(@str,1,PATINDEX('%[A-Z]%',@str)))-2)
Jim
Everyday I learn something that somebody else already knew
Hi Jim,
Thanks for this try, but I think due to the various formatting my data has, it gives me a sub string function error.
Best,
kml |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
52326 Posts |
Posted - 06/20/2012 : 20:28:37
|
quote:
Originally posted by xhostx
quote:
Originally posted by visakh16
then would suggest you do it as batches for each of different formats
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Hi visak,
What did you please mean with batches, would that be cursors or while loops?
Thanks,
kml
not either of them
I meant creating temporary table, separate update statements for covering each condition in it and finally doing select from table
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
charindex