Reading Record from last - ntext

[macsql1]

Hi,

I have two NTEXT field. Field length is not constant. I wanted to read only last 5 records from right side.

ie, 90021


id={E80D4A7B-C8FF-DB11-A6FC-00188B2E03D9}&etc=90021
cr=FromType=ateFromId={W780D8A7-F99D-DB11-8303-00188B2E03D9}&etc=90021

Can any body help ? Thanks you in advance

-Mac

 

[bborissov]

You mixed up records with field values, no?
Try:

SELECT RIGHT(YourNTextField,5) AS test
FROM YourTable

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

 

[bborissov]

Ignore my post, sorry!
I read NTEXT but thing of NVARCHAR!!!



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

 

[macsql1]

Yes Borislav, type is NTEXT.

I wanted to read Ntext field.

-Mac

 

[ca8msm]

Depending what data the column contains, you could still use the suggestion provided above, but convert it to a varchar first:

declare @table1 table (myvalue ntext)

insert @table1 values('123456789')
insert @table1 values('987654321')

select right(cast(myvalue as varchar),5) from @table1

However, I think this will be dependant on the data in your table as to whether it works or not.

Next time, consider using the correct structure in the first place to avoid these types of problems.


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

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[bborissov]

Just a small typo in Mark's answer, when you CAST or CONVERT types always add the length (there where it is appropriate), because SQL Server will convert it to its default values:

SELECT RIGHT(CAST(YourNTextField as varchar(8000),5) AS test
FROM YourTable

I hope 8000 chars will be enough.

I am with Mark here Do not use TEXT, IMAGE or NTEXT types, use varchar, nvarchar or varbinary types instead. If you use SQL Server 2000 you are stuck with the maximum allowed chars in these types but in SQL Server 2005 you could use N/varchar(max).

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

 

[macsql1]

Thank you all for your suggestion.

-Mac

 

[gmmastros]

text and ntext are difficult to work with because many of the available string handling functions don't work with that data type. If you are using SQL Server 2005, then I recommend you change the data type to nvarchar(max). There is a way to make the query work while not having to worry about the length of the string contained within the ntext column.

If this column was a varchar(100), and you wanted to get the last 5 characters, the answer would be simple.

[tt][blue]Select Right(Column, 5)
From TableName[/blue][/tt]

Unfortunately, this code will NOT work for a TEXT or NTEXT column. Why? Because the Right function does not support that data type. You would get this error.

[tt][!]Server: Msg 8116, Level 16, State 2, Line 11[/!]
Argument data type ntext is invalid for argument 1 of len function.[/tt]

Fortunately, there is a SubString function that you can use. Unfortunately, you need to provide the starting position in order to use it effectively. Again... if you had a varchar column, you could use this.

[tt][blue]Select SubString(Column, Len(Column)-5, 5)[/blue][/tt]

Unfortunately, this doesn't work either because you cannot supply a text or ntext column to the LEN function.

So, as you can see, there are a lot of problems with using Text or nText data types. If you can change the column to another data type, then I would recommend you do so. There is, of course, another way to accommodate this query.

SQL Server has a DataLength function that you can use. In this situation, you need to understand the difference between Len and DataLength. Len will return the number of characters that are contained within a 'string' variable. DataLength will return the number of BYTES required to store the data. There is a subtle, yet important distinction. The difference between text and [!]n[/!]text is that ntext can accommodate unicode characters. unicode requires 2 bytes per character to store, so DataLength will return double the number of characters because it takes 2 bytes to store each one.

To see what I mean, copy/paste this to query analyzer.

Declare @temp nvarchar(20)

Set @Temp = 'Hello'

Select Len(@Temp), DataLength(@Temp)

When you run that code, you will get:

[tt][blue]Len_Output DataLength_Output
----------- -----------------
5 10

(1 row(s) affected)[/blue][/tt]

I mentioned SubString earlier in this post. SubString does work with ntext, so we can use that function. The second parameter to the SubString function specifies the starting position (in characters). So, we will need to calculate that. DataLength can be used for this, but remember that it returns the number of bytes, so we will need to divide by 2.

Finally, to get the last 5 digits of an ntext column, you can use this...

Select SubString(ColumnName, DataLength(ColumnName)/2 - 4, 5)
From   TableName

Make sense?


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[macsql1]

George, Exactly am looking for same. Thanking you

-Mac