Solved

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

Posted on 2006-10-23
6
580 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

707 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