Solved

How can I check the length of my text fields in SQL Server vs. PostGRE?

Posted on 2006-10-23
6
570 Views
Last Modified: 2012-08-14
I am migrating an older PostGRESQL database into a SQL Server database using a DTS package.  I'm using a PostGRE driver on my PC that will allow me to connect via ODBC to my PostGRE database.  I've successfully copied my tables over but my coworker brought up a point that he could not get text fields to copy over correctly.  

I want to test my migration to see if the text fields did in fact come accross correctly.  I want to query one of my tables and find out the length of characters within one of the text columns.  I can't seem to find a function that will do this.

I've tried using the len() function but it doesn't work in SQL Server.  

Select len(c_problemText) as txtlen
from mytable

I get an error saying "Argument data type text is invalid for argument 1 of len function."


What I really need is some way to verify that the text fields I brought accross to SQL Server are not being truncated.  If anyone knows a way I could verify this I could really use some suggestions.

Thanks!
0
Comment
Question by:Ike23
  • 2
  • 2
  • 2
6 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 17792164
Use:
Select DATALENGTH(c_problemText) as txtlen
from mytable
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17792172
>>What I really need is some way to verify that the text fields I brought accross to SQL Server are not being truncated.  If anyone knows a way I could verify this I could really use some suggestions.<<
Perhaps you can link the two servers and compare lengths with a query.
0
 
LVL 11

Expert Comment

by:rw3admin
ID: 17792185
TEXT datatype is like CHAR, it will occupy whatever current allocation size you have identified in SQL
so your best bet is to convert TEXT to varchar and then do a Len(Str) on this field. provided your text field has less or equal to 8000 charactes, incase its more then 8000 characters you need to write a function that will splice Text field in 8000 chunks

From BOL
D. Return specific text data
This example locates the text column (pr_info) associated with pub_id 0736 in the pub_info table of the pubs database. It first declares the local variable @val. The text pointer (a long binary string) is then put into @val and supplied as a parameter to the READTEXT statement, which returns 10 bytes starting at the fifth byte (offset of 4).

USE pubs
GO
DECLARE @val varbinary(16)
SELECT @val = TEXTPTR(pr_info)
FROM pub_info
WHERE pub_id = '0736'
READTEXT pub_info.pr_info @val 4 10
GO


 
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 11

Expert Comment

by:rw3admin
ID: 17792198
disregard my solution, Anthony (acperkins) is right,
0
 
LVL 4

Author Comment

by:Ike23
ID: 17792207
Thanks aceperkins, the DATALENGTH worked great.

I have a front end web page that I can use to post SQL code against my PostGRESQL database also but I don't know the correct function to use to get a text count.  If I can do the same sore of query on my PostGRE side then I can compare and that's really all I need.

Ike
0
 
LVL 4

Author Comment

by:Ike23
ID: 17792686
I found the PostGRESQL function also.  It was just LENGTH().  Thanks!

0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question