Last 8000 characters

[bhp]

Hi does anyone now how to return the last 8000 characters from a ntext field I guess you would use reverse in some fashion?
Any help greatly appreciated as giving me a headache
Thanks - James

 

[bborissov]

SELECT RIGHT(CAST(TextField as varchar(8000)), 8000)
 FROM ...

Not tested

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

 

[bhp]

Hi thanks fro response but that gives me the FIRST 8000 characters. e.g
if I wanted the last 5 digits when the text in the field was "football is great" I only want to return the word "great" in this case.
I need to return the LAST 8000 characters, sorry if not being clear, thanks - James

 

[bborissov]

declare @test varchar(500)
SET @test = 'football is great'
SELECT RIGHT(@test,5)

What is the result?
Did you get the LAST 5 chars?

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

 

[SQLDenis]

bborissov , he is talking about ntext, you can't use right 8000 on a ntext column

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[gmmastros]

There are a limited number of functions that work properly on an nText data type. However, SubString and DataLength work, so using a combination of these 2 functions should help you out.

Ex:

[COLOR=blue]declare[/color] @Temp [COLOR=blue]Table[/color](data ntext)

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'football is great'[/color])

[COLOR=blue]Select[/color] [COLOR=#FF00FF]substring[/color](data, [COLOR=#FF00FF]datalength[/color](data)/2 + 1 -[!]5[/!], [COLOR=#FF00FF]datalength[/color](data)/2) [COLOR=blue]From[/color] @Temp

The example above returns the last 5 characters from an nText column. If you want to change this to the last 8000 characters, simply replace the [!]5[/!] above with 8000.

Make sense?

-George

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

 

[bborissov]

declare @Temp Table(data ntext)
Insert Into @Temp Values('football is great')
Select RIGHT(CAST(data as varchar(8000)),5)
FROM @Temp

BUT of course if you have more than 8000 chars in Data field that wouldn't work.

BTW what version of SQL Server?
If you use SQL Server 2005 why not convert all these columns TEXT,. NTEXT, IMAGE to varchar(max), nvarchar(max) and varbinary(max)?

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

 

[bhp]

Many thanks but this does not seem to work in my case the field can have 20,000 + characters in it, I just need the last 8000 in each case, this seems to start 8000 characters into the text. I guess my query needs to start at the end and work backwards for 8000 characters?
Sorry again if not being clear here, and thanks again !

 

[SQLDenis]

ntext is a big pain in the neck, I managed to avoid it all my life after watching coworkers pull out there hair

Lookup READTEXT and TEXTPTR in BOL, you will have to do this in chunks

In SQL server 2005 there is a new datatype varchar(max) and nvarchar(max) which don't have all these limitations of text and ntext

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[bhp]

Thanks everyone for help on this much appreciated going to attack another way. Hoping to help more folks on here myself

 

[gmmastros]

bhp,

Did you try the code I presented? It should work well for you.

-George

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

 

[SQLDenis]

Here is proof that George's code will work

declare @Temp Table(data ntext)
Insert Into @Temp Values('Planning for Application Development
When planning replication applications, consider the following: 

Design your application to minimize conflicts. If the Subscribers need to read data and do not need to update data, conflicts will be avoided. Partitioning data logically according to geographic locations or business uses can also prevent users from updating the same data values, thus avoiding conflicts. 


For online/offline applications where you expect conflicts can and will occur, merge replication is usually the best choice for your application. Merge replication allows for a variety of conflict detection and resolution policies, evaluates updates row by row, and results in data convergence. 


Snapshot replication or transactional replication with the immediate updating or queued updating option is recommended for applications that are mostly read with occasional updates. Immediate updating uses two-phase commit (2PC). Queued updating provides policies for conflict resolution and evaluates updates and conflicts on a transaction basis. 


When using merge replication, or when using snapshot replication or transactional replication with the queued updating option, determine the conflict resolution policy before implementing replication.


Research how disconnecting from the database will affect mobile or disconnected users. What happens if users do not immediately see the updates they make at the Subscriber? 


How fast is data synchronization? How long does it take to apply the initial snapshot and how long does it take for periodic updates? Test the initial snapshot by applying it over the actual network that will be used. Consider applying the initial snapshot manually using a CD-ROM or removable media device if transferring it over the network takes too long. 


Manage identity values by using identity ranges when using merge replication or when using snapshot replication or transactional replication and allowing queued updating subscriptions. If you create data partitions and assign different identity ranges to the partitions, conflicts will be avoided because different sites will be working with different subsets of data.


Ensure that your applications use column names in INSERT statements before enabling merge replication or transactional replication with immediate updating or queued updating options, because these types of replication may add columns to your publishing table. If you do not list the column names in INSERT statements for these types of replication, an error will occur.


If you are using transactional replication with the immediate updating or queued updating option, Subscribers will not be able to update values with the text or image data types. The publication can contain text or image columns, but those columns may be updated only at the Publisher.


Be aware of maximum column and row sizes. A table used in snapshot replication or transactional replication can have a maximum of 255 columns and a maximum row size of 8,000 bytes. A table used in a merge publication can have a maximum of 246 columns and a maximum row size of 6,000 bytes. The reason the restriction for merge replication is stricter than the restriction for transactional replication is because conflict tables have the same structure with additional columns that store information about the origin of the conflict and the specific reason for the conflict. Because additional space is needed to record this conflict information, the maximum row size is less than the maximum row size for transactional replication.


If you will have a high volume of transactions, always design your application to use stored procedures to modify data at the Publisher and publish the execution of stored procedures. 


Planning for Application Development
When planning replication applications, consider the following: 

Design your application to minimize conflicts. If the Subscribers need to read data and do not need to update data, conflicts will be avoided. Partitioning data logically according to geographic locations or business uses can also prevent users from updating the same data values, thus avoiding conflicts. 


For online/offline applications where you expect conflicts can and will occur, merge replication is usually the best choice for your application. Merge replication allows for a variety of conflict detection and resolution policies, evaluates updates row by row, and results in data convergence. 


Snapshot replication or transactional replication with the immediate updating or queued updating option is recommended for applications that are mostly read with occasional updates. Immediate updating uses two-phase commit (2PC). Queued updating provides policies for conflict resolution and evaluates updates and conflicts on a transaction basis. 


When using merge replication, or when using snapshot replication or transactional replication with the queued updating option, determine the conflict resolution policy before implementing replication.


Research how disconnecting from the database will affect mobile or disconnected users. What happens if users do not immediately see the updates they make at the Subscriber? 


How fast is data synchronization? How long does it take to apply the initial snapshot and how long does it take for periodic updates? Test the initial snapshot by applying it over the actual network that will be used. Consider applying the initial snapshot manually using a CD-ROM or removable media device if transferring it over the network takes too long. 


Manage identity values by using identity ranges when using merge replication or when using snapshot replication or transactional replication and allowing queued updating subscriptions. If you create data partitions and assign different identity ranges to the partitions, conflicts will be avoided because different sites will be working with different subsets of data.


Ensure that your applications use column names in INSERT statements before enabling merge replication or transactional replication with immediate updating or queued updating options, because these types of replication may add columns to your publishing table. If you do not list the column names in INSERT statements for these types of replication, an error will occur.


If you are using transactional replication with the immediate updating or queued updating option, Subscribers will not be able to update values with the text or image data types. The publication can contain text or image columns, but those columns may be updated only at the Publisher.


Be aware of maximum column and row sizes. A table used in snapshot replication or transactional replication can have a maximum of 255 columns and a maximum row size of 8,000 bytes. A table used in a merge publication can have a maximum of 246 columns and a maximum row size of 6,000 bytes. The reason the restriction for merge replication is stricter than the restriction for transactional replication is because conflict tables have the same structure with additional columns that store information about the origin of the conflict and the specific reason for the conflict. Because additional space is needed to record this conflict information, the maximum row size is less than the maximum row size for transactional replication.


If you will have a high volume of transactions, always design your application to use stored procedures to modify data at the Publisher and publish the execution of stored procedures. 


Planning for Application Development
When planning replication applications, consider the following: 

Design your application to minimize conflicts. If the Subscribers need to read data and do not need to update data, conflicts will be avoided. Partitioning data logically according to geographic locations or business uses can also prevent users from updating the same data values, thus avoiding conflicts. 


For online/offline applications where you expect conflicts can and will occur, merge replication is usually the best choice for your application. Merge replication allows for a variety of conflict detection and resolution policies, evaluates updates row by row, and results in data convergence. 


Snapshot replication or transactional replication with the immediate updating or queued updating option is recommended for applications that are mostly read with occasional updates. Immediate updating uses two-phase commit (2PC). Queued updating provides policies for conflict resolution and evaluates updates and conflicts on a transaction basis. 


When using merge replication, or when using snapshot replication or transactional replication with the queued updating option, determine the conflict resolution policy before implementing replication.


Research how disconnecting from the database will affect mobile or disconnected users. What happens if users do not immediately see the updates they make at the Subscriber? 


How fast is data synchronization? How long does it take to apply the initial snapshot and how long does it take for periodic updates? Test the initial snapshot by applying it over the actual network that will be used. Consider applying the initial snapshot manually using a CD-ROM or removable media device if transferring it over the network takes too long. 


Manage identity values by using identity ranges when using merge replication or when using snapshot replication or transactional replication and allowing queued updating subscriptions. If you create data partitions and assign different identity ranges to the partitions, conflicts will be avoided because different sites will be working with different subsets of data.


Ensure that your applications use column names in INSERT statements before enabling merge replication or transactional replication with immediate updating or queued updating options, because these types of replication may add columns to your publishing table. If you do not list the column names in INSERT statements for these types of replication, an error will occur.


If you are using transactional replication with the immediate updating or queued updating option, Subscribers will not be able to update values with the text or image data types. The publication can contain text or image columns, but those columns may be updated only at the Publisher.


Be aware of maximum column and row sizes. A table used in snapshot replication or transactional replication can have a maximum of 255 columns and a maximum row size of 8,000 bytes. A table used in a merge publication can have a maximum of 246 columns and a maximum row size of 6,000 bytes. The reason the restriction for merge replication is stricter than the restriction for transactional replication is because conflict tables have the same structure with additional columns that store information about the origin of the conflict and the specific reason for the conflict. Because additional space is needed to record this conflict information, the maximum row size is less than the maximum row size for transactional replication.


If you will have a high volume of transactions, always design your application to use stored procedures to modify data at the Publisher and publish the execution of stored procedures.')



Select datalength(data)/2,substring(data, datalength(data)/2 + 1 -5, datalength(data)/2) From @Temp

it returns the last 5 characters which are "ures."

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[bhp]

Excellent thanks v much for help