Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 charindex		  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

xhostx
Constraint Violating Yak Guru

USA
277 Posts
Posted - 06/18/2012 :  15:45:54  Show Profile  Reply with Quote
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  Show Profile  Reply with Quote 

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/
Go to Top of Page

xhostx
Constraint Violating Yak Guru

USA
277 Posts
Posted - 06/19/2012 :  08:12:39  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts
Posted - 06/19/2012 :  14:16:09  Show Profile  Reply with Quote
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/
Go to Top of Page

xhostx
Constraint Violating Yak Guru

USA
277 Posts
Posted - 06/19/2012 :  15:04:11  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts
Posted - 06/19/2012 :  15:08:39  Show Profile  Reply with Quote
then would suggest you do it as batches for each of different formats

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2875 Posts
Posted - 06/19/2012 :  15:23:50  Show Profile  Reply with Quote
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
Go to Top of Page

xhostx
Constraint Violating Yak Guru

USA
277 Posts
Posted - 06/20/2012 :  08:05:34  Show Profile  Reply with Quote
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
Go to Top of Page

xhostx
Constraint Violating Yak Guru

USA
277 Posts
Posted - 06/20/2012 :  08:12:26  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts
Posted - 06/20/2012 :  20:28:37  Show Profile  Reply with Quote
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/
Go to Top of Page
  Previous Topic Topic Next 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.05 seconds. Powered By: Snitz Forums 2000