Question about position in ntext field

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

Transact-SQL (2000)

Question about position in ntext field

Author

Topic

stevo_3
Starting Member

20 Posts

Posted - 2006-01-18 : 13:48:01

Hello all,

i got a table workitem with a field comment (ntext),

content of comment(example):
<STIB> 0502R314231 <LOCATION> STIB TOR 15 3e av.
<FIRSTNAME> NATHALIE<LASTNAME> HENRY <PHONE>021465656 <FAX> 021565465

what i want the number of characters between <FIRSTNAME> and <LASTNAME>, here it would be 9 (space + NATHALIE),

is there a function who can do this,
i know with charindex you can do a lookup for a string field but it is not exactly what i want, i tested it already

anyone has an idea?

Thx

S

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2006-01-18 : 13:58:55

Couldn't you find the CharIndex of the two "tags" and then get the difference?

________________________________________________
The only cure for thinking is picking rocks, or drinking beer.

stevo_3
Starting Member

20 Posts

Posted - 2006-01-18 : 14:19:40

hello,

you mean something like this:

charindex('<FIRSTNAME>',comment,1) - charindex('<LASTNAME>',comment,1)

i don't know exactly what charindex is returning?
is it returning a int then it is ok,
but when it is returning another data type i'll have problems

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2006-01-18 : 14:26:53

quote:
Originally posted by stevo_3

hello,

you mean something like this:

charindex('<FIRSTNAME>',comment,1) - charindex('<LASTNAME>',comment,1)

i don't know exactly what charindex is returning?
is it returning a int then it is ok,
but when it is returning another data type i'll have problems

BOL is your friend (it certainly is mine)

CHARINDEX
Returns the starting position of the specified expression in a character string.

Syntax
CHARINDEX ( expression1 , expression2 [ , start_location ] )

Arguments
expression1

Is an expression containing the sequence of characters to be found. expression1 is an expression of the short character data type category.

expression2

Is an expression, usually a column searched for the specified sequence. expression2 is of the character string data type category.

start_location

Is the character position to start searching for expression1 in expression2. If start_location is not given, is a negative number, or is zero, the search starts at the beginning of expression2.

Return Types
int

Remarks
If either expression1 or expression2 is of a Unicode data type (nvarchar or nchar) and the other is not, the other is converted to a Unicode data type.

If either expression1 or expression2 is NULL, CHARINDEX returns NULL when the database compatibility level is 70 or later. If the database compatibility level is 65 or earlier, CHARINDEX returns NULL only when both expression1 and expression2 are NULL.

If expression1 is not found within expression2, CHARINDEX returns 0.

Examples
The first code example returns the position at which the sequence "wonderful" begins in the notes column of the titles table. The second example uses the optional start_location parameter to begin looking for wonderful in the fifth character of the notes column. The third example shows the result set when expression1 is not found within expression2.

USE pubs
GO
SELECT CHARINDEX('wonderful', notes)
FROM titles
WHERE title_id = 'TC3218'
GO

-- Use the optional start_location parameter to start searching
-- for wonderful starting with the fifth character in the notes
-- column.
USE pubs
GO
SELECT CHARINDEX('wonderful', notes, 5)
FROM titles
WHERE title_id = 'TC3218'
GO

Here is the result set for the first and second queries:

-----------
46

________________________________________________
The only cure for thinking is picking rocks, or drinking beer.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources