Solved

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

Posted on 2006-10-23
6
568 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

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.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

867 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now