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