Find the last character of a Text data type field 1

[eao]

I am trying to figure out the expression to identify the position of the last character in a text data type field so that I may pass the result to a SUBSTRING function. I have not had any success. I tried to do PATINDEX (RIGHT (fieldname, 1), fieldname), but I got an invalid datatype message. Any ideas?

 

[kaht]

len(rtrim(rtrim(columnName)))

-kaht

http://www.silkypups.com

[small](All puppies have now found loving homes, thanks for all who showed interest)[/small]

 

[kaht]

actually, you'd not want the ltrim in there, just this would work:

len(rtrim(columnName))

[small]which is irrelevant because I typed 2 rtrims above, doh[/small]

-kaht

http://www.silkypups.com

[small](All puppies have now found loving homes, thanks for all who showed interest)[/small]

 

[eao]

kaht,

When I try the above statement, I get the following message in QA:

Server: Msg 8116, Level 16, State 2, Line 5
Argument data type text is invalid for argument 1 of rtrim function.

 

[DotNetGnat]

Select substring(mytextfield, datalength(mytextfield)-1, datalength) as the LastCharacter from mytable

-DNG

 

[DotNetGnat]

len() does not work on text fields...

-DNG

 

[eao]

Thanks for getting me on the right track, DotNetGnat. Here's the code I wound up using:

SELECT SUBSTRING (mytextfield, 1, datalengeth(mytextfield)) from mytable

 

[bborissov]

But that way you get whole text field, not only the last char.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[DotNetGnat]

no problem...glad to assist...

-DNG

 

[gmmastros]

DNG,

That's close, but you missed it by a little.

Declare @Temp Table(Data Text)

Insert Into @Temp Values('hello world')
Insert Into @Temp Values('hello world ')

Select SubString(Data, DataLength(Data), 1)
From   @Temp

You don't want to subtract 1 from the data length and the 3rd parameter to the substring function is length, so 1 works well.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bborissov]

Based on DNG code:

declare @test TABLE (Tst Text)
INSERT INTO @Test VALUES ('aaaaaaaaaaaaaaaaav')
SELECT SUBSTRING(Tst, datalength(Tst),1)
    FROM @Test

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[gmmastros]

Boris, I like the way you think.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[DotNetGnat]

oh yeah...i was thinking that i am overusing the datalength() function but didnt think of the way you suggested...

-DNG

 

[bborissov]

George,

Great minds ...

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[eao]

Borris,

The code I posted above is sufficient for me because I was in fact trying to get the entire text of the field. I needed to know the position of the last character to pass to the substring function, and as it turns out, the datalengeth function was all I was missing. Thanks to you and everyone else for their assistance!

 

[DotNetGnat]

eao,

but there is one problem with the code you are using...

substring() gives only first 2000 characters...so if the datalength of your text is greater than 2000 characters than you will get only the first 2000 characters...

so make sure about this...

-DNG

 

[eao]

DNG,

Wow. That is very good to know. Should I have a second query that reads as follows?

SELECT SUBSTRING (mytextfield, 2001, datalengeth(mytextfield)) from mytable

 

[DotNetGnat]

I know its a pain to work with text fields in SQL Server...I guess the answer for your question would be yes...

-DNG

 

[eao]

Thanks DNG.

I ran this:

select max (datalength (mytextfield)) from mytable

and found that the largest datalength is 8758 characters, so it looks like I'll need a few of those queries to get the job done.