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

General SQL Server Forums

New to SQL Server Programming

charindex

Author

Topic

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-06-18 : 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

52326 Posts

Posted - 2012-06-18 : 23:08:25

[code]
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
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-06-19 : 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

52326 Posts

Posted - 2012-06-19 : 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

277 Posts

Posted - 2012-06-19 : 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

52326 Posts

Posted - 2012-06-19 : 15:08:39

then would suggest you do it as batches for each of different formats

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-06-19 : 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

277 Posts

Posted - 2012-06-20 : 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

277 Posts

Posted - 2012-06-20 : 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

52326 Posts

Posted - 2012-06-20 : 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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources