close on CHARINDEX! need a push

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

SQL Server 2000 Forums

SQL Server Development (2000)

close on CHARINDEX! need a push

Author

Topic

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2004-03-05 : 15:10:25

I've got CHARINDEX working to the point where I can locate columns 3 and 4 in my text file (this text file is tab delimited and first loaded into single col table before I use charindex to grab specific columns. see this link for history:

http://sqlteam.com/forums/topic.asp?TOPIC_ID=33037

below are snippets of the SUBSTRING syntax that is retrieving the columns. I can successfully retrieve columns 3 and 4, but when I try to retrieve column 6 using the code below, column 4 is returned again. any help is appreciated. thx.

finds third column no problem:

SUBSTRING(Col1

,(CHARINDEX(CHAR(9),Col1,(CHARINDEX(CHAR(9),Col1)+1))) +1

=================================================================

finds fourth column no problem:

SUBSTRING(Col1

,(CHARINDEX(CHAR(9),Col1,(CHARINDEX(CHAR(9),Col1)+1)+(CHARINDEX(CHAR(9),Col1)+1))) +1

====================================================================

finds fourth column again when I want it to find SIXTH column

SUBSTRING(Col1

,(CHARINDEX(CHAR(9),Col1,(CHARINDEX(CHAR(9),Col1)+1)+(CHARINDEX(CHAR(9),Col1)+1)+(CHARINDEX(CHAR(9),Col1)+1)+(CHARINDEX(CHAR(9),Col1)+1))) +1

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-05 : 16:08:58

[code]

USE Northwind
GO

CREATE FUNCTION udf_GetWord (
@str nvarchar(4000)
, @Word int
)
RETURNS nvarchar(4000)
AS
BEGIN
DECLARE @LastPosition int, @CHAR9Found int, @Start int, @End int, @WordFound nvarchar(4000)

SET @LastPosition = 0
SET @CHAR9Found = 0
WHILE (@CHAR9Found < @Word-1)
BEGIN
IF (CHARINDEX(CHAR(9), @str, @LastPosition + 1) = 0)
BREAK

ELSE
BEGIN
SET @LastPosition = CHARINDEX(CHAR(9), @str, @LastPosition + 1)
SET @CHAR9Found = @CHAR9Found + 1
END

END

SET @Start = @LastPosition + 1
SET @End = CHARINDEX(CHAR(9), @str, @LastPosition + 1) - @Start

SELECT @WordFound = SUBSTRING(@str,@start,@end)
RETURN @WordFound
END
GO

DECLARE @x nvarchar(4000)

SELECT @x = 'A'+CHAR(9)+'Man'+CHAR(9)+'asked'+CHAR(9)+'For'+CHAR(9)+'a'+CHAR(9)+'margaritta'
+CHAR(9)+'without'+CHAR(9)+'salt'

SELECT dbo.udf_GetWord(@x,6)
GO

DROP FUNCTION udf_GetWord
GO

[/code]

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources