Advanced T-SQL

Question

Post reply

Advanced T-SQL

rs

SSCrazy

Points: 2819
More actions
December 11, 2003 at 9:20 am

#83604

Hi,

I have a text field for example

aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa(1)aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa(1)aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa(1)aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

I need find out the position of last occurrence of (1) in above text, is there any easiest way to do.

I know can find with loop looking for different method. Any ideas appreciated.

Thanks in advance.

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 728159 More actions · Answer 1

no easy way in TSQL that I can think of. If you need to do this for a set, it will be cumbersome. Some type of loop or cursor might be needed.

Steve Jones

sjones@sqlservercentral.com

http://qa.sqlservercentral.com/columnists/sjones

The Best of SQL Server Central.com 2002 - http://qa.sqlservercentral.com/bestof/

http://www.dkranch.net

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

Gil_Adi SSCommitted Points: 1669 More actions · Answer 2

The Follwing Exmplw May Be of Some Use

--------------------------

-- Aggregate Bitwise OR --

--------------------------

-- data and schema

CREATE TABLE Bitvalues

(

key_col int NOT NULL PRIMARY KEY,

group_col int NOT NULL,

data_bits int NOT NULL

)

INSERT INTO Bitvalues VALUES(1, 1, 1) -- ...0001

INSERT INTO Bitvalues VALUES(2, 1, 9) -- ...1001

INSERT INTO Bitvalues VALUES(3, 1, 2) -- ...0010

INSERT INTO Bitvalues VALUES(4, 2, 8) -- ...1000

INSERT INTO Bitvalues VALUES(5, 2, 3) -- ...0011

INSERT INTO Bitvalues VALUES(6, 2, 7) -- ...0111

INSERT INTO Bitvalues VALUES(7, 3, 4) -- ...0100

INSERT INTO Bitvalues VALUES(8, 3, 6) -- ...0110

INSERT INTO Bitvalues VALUES(9, 3, 5) -- ...0101

-- desired output

group_col or_data_col

----------- -----------

1 11 -- ...1011

2 15 -- ...1111

3 7 -- ...0111

-- auxiliary table

CREATE TABLE Bits

(

bin_val BINARY(4) NOT NULL PRIMARY KEY

)

INSERT INTO Bits VALUES(0x00000001)

INSERT INTO Bits VALUES(0x00000002)

INSERT INTO Bits VALUES(0x00000004)

INSERT INTO Bits VALUES(0x00000008)

INSERT INTO Bits VALUES(0x00000010)

INSERT INTO Bits VALUES(0x00000020)

INSERT INTO Bits VALUES(0x00000040)

INSERT INTO Bits VALUES(0x00000080)

INSERT INTO Bits VALUES(0x00000100)

INSERT INTO Bits VALUES(0x00000200)

INSERT INTO Bits VALUES(0x00000400)

INSERT INTO Bits VALUES(0x00000800)

INSERT INTO Bits VALUES(0x00001000)

INSERT INTO Bits VALUES(0x00002000)

INSERT INTO Bits VALUES(0x00004000)

INSERT INTO Bits VALUES(0x00008000)

INSERT INTO Bits VALUES(0x00010000)

INSERT INTO Bits VALUES(0x00020000)

INSERT INTO Bits VALUES(0x00040000)

INSERT INTO Bits VALUES(0x00080000)

INSERT INTO Bits VALUES(0x00100000)

INSERT INTO Bits VALUES(0x00200000)

INSERT INTO Bits VALUES(0x00400000)

INSERT INTO Bits VALUES(0x00800000)

INSERT INTO Bits VALUES(0x01000000)

INSERT INTO Bits VALUES(0x02000000)

INSERT INTO Bits VALUES(0x04000000)

INSERT INTO Bits VALUES(0x08000000)

INSERT INTO Bits VALUES(0x10000000)

INSERT INTO Bits VALUES(0x20000000)

INSERT INTO Bits VALUES(0x40000000)

INSERT INTO Bits VALUES(0x80000000)

-- breaking up the values in the Bitvalues table

SELECT *

FROM Bitvalues JOIN Bits

ON data_bits & bin_val = bin_val

key_col group_col data_bits bin_val

----------- ----------- ----------- ----------

1 1 1 0x00000001

2 1 9 0x00000001

2 1 9 0x00000008

3 1 2 0x00000002

4 2 8 0x00000008

5 2 3 0x00000001

5 2 3 0x00000002

6 2 7 0x00000001

6 2 7 0x00000002

6 2 7 0x00000004

7 3 4 0x00000004

8 3 6 0x00000002

8 3 6 0x00000004

9 3 5 0x00000001

9 3 5 0x00000004

-- Aggregate Bitwise OR

SELECT

group_col,

SUM(DISTINCT(CAST(bin_val AS int))) AS or_data_col

FROM Bitvalues JOIN Bits

ON data_bits & bin_val = bin_val

GROUP BY group_col

noeld SSC Guru Points: 96590 More actions · Answer 3

noeld

SSC Guru

Points: 96590

December 11, 2003 at 9:42 am

#485754

is that a TEXT type or a varchar type?

* Noel

rs SSCrazy Points: 2819 More actions · Answer 4

rs

SSCrazy

Points: 2819

December 11, 2003 at 9:48 am

#485757

TEXT type

stubob SSCommitted Points: 1839 More actions · Answer 5

if it's under 8000 bytes, you can convert to varchar & process:

SELECT datalength(textCol) -

charindex(')1(',reverse(cast(textCol as varchar(8000))))

FROM textTable

Otherwise I suppose it can be done with TEXTPTR.

Seems a shame because there is PATINDEX, which is great for finding the first occurrence.

The only normal people are those you don't know well - Oscar Wilde

Data: Easy to spill, hard to clean up!

ZenDada SSCarpal Tunnel Points: 4637 More actions · Answer 6

SELECT DATALENGTH('aa(1)aaaaa(1)aaaaa')-PATINDEX('%[(1)]%', REVERSE('aa(1)aaaaa(1)aaaaa')) --will give you the position of the '1' in the middle.

You will still have to jack with it a bit depending on whether you want the position of the '1' or '(' or ')', but you get the idea. If the pattern is always '(1)' then you only need to add + whatever at the end. Otherwise you may need to use some substring function or other.

Jules

[font="Courier New"]ZenDada[/font]

rs SSCrazy Points: 2819 More actions · Answer 7

It is working fine the example you gave but not on my data.

My data looks like the following

SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING

(1)-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING

-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING

(2)-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING

-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING

(3)-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING

-SOMETHING-SOMETHING-SOMETHING

(4)-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING

-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING

(5)-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING

--------------------------------------------------------------------------------------------------

-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING

(1)-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING

-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING

(2)-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING

-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING

(3)-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING

-SOMETHING-SOMETHING-SOMETHING

(4)-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING

-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING

(5)-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING

-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING

--------------------------------------------------------------------------------------------------

-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING

(1)-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING

-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING

(2)-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING

-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING

(3)-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING

-SOMETHING-SOMETHING-SOMETHING

(4)-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING

-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING

(5)-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING

-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING

--------------------------------------------------------------------------------------------------

-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING

(1)-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING

-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING

(2)-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING

-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING

(3)-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING

-SOMETHING-SOMETHING-SOMETHING

(4)-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING

-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING

(5)-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING

-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING

I need to find out the last occurrence of last (1)%(5), Any ideas appreciated.

Thanks in advance.

ZenDada SSCarpal Tunnel Points: 4637 More actions · Answer 8

ZenDada

SSCarpal Tunnel

Points: 4637

December 11, 2003 at 1:49 pm

#485832

I'm sorry, please explain. (1)%(5) refers to the entire bulleted list? So you want to find the position of the '1'? The '5'? Or both? Or what?

Jules

[font="Courier New"]ZenDada[/font]

rs SSCrazy Points: 2819 More actions · Answer 9

I am sorry did not posted properly,I want to find out the last position of following paragraph

(1)-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING

-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING

(2)-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING

-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING

(3)-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING

-SOMETHING-SOMETHING-SOMETHING

(4)-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING

-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING

(5)-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING-SOMETHING

Thanks

ZenDada SSCarpal Tunnel Points: 4637 More actions · Answer 10

ZenDada

SSCarpal Tunnel

Points: 4637

December 11, 2003 at 1:57 pm

#485834

Then maybe you want the line feed character at the end of the last line in bullet(5)?

Jules

[font="Courier New"]ZenDada[/font]

rs SSCrazy Points: 2819 More actions · Answer 11

rs

SSCrazy

Points: 2819

December 11, 2003 at 2:08 pm

#485840

I did not follow you, Could you please explain bit more.

Thanks

ZenDada SSCarpal Tunnel Points: 4637 More actions · Answer 12

ZenDada

SSCarpal Tunnel

Points: 4637

December 11, 2003 at 2:33 pm

#485845

Position needs to refer to A SINGLE character somewhere. What character do you want to find? The '5'? The'g' at the end of the sentence? The first character AFTER the 'g' which is the line feed character? Or what?

Jules

[font="Courier New"]ZenDada[/font]

ThomasH SSCertifiable Points: 7437 More actions · Answer 13

ThomasH

SSCertifiable

Points: 7437

December 11, 2003 at 2:52 pm

#485849

1. What is the Max(DataLength()) you would be dealing with?

2. Is the data CR/LF delimited, or other delimiter?

3. Is there any guarantee that the ending 8000 characters of the data will have what you are looking for?

Once you understand the BITs, all the pieces come together

ZenDada SSCarpal Tunnel Points: 4637 More actions · Answer 14

DECLARE @test-2 AS VARCHAR(8000)

DECLARE @TEST2 AS VARCHAR(8000)

DECLARE @POS AS INT

DECLARE @POS2 AS INT

SELECT @test-2 = (SELECT CAST(TEST AS VARCHAR(8000)) FROM TBL_TEST)

SET @POS = (SELECT DATALENGTH(@TEST)-PATINDEX('%[1-9]%', REVERSE(@TEST))+1)

SELECT @TEST2 = (SELECT SUBSTRING(@TEST, @POS, DATALENGTH(@TEST)))

SET @POS2 = CHARINDEX(CHAR(13), @TEST2)+ @POS

I think that will give you the position of the first carriage return after the last bullet number or thereabouts. Play with it.

Jules

[font="Courier New"]ZenDada[/font]