Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 586
  • Last Modified:

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

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
Ike23
Asked:
Ike23
  • 2
  • 2
  • 2
1 Solution
 
Anthony PerkinsCommented:
Use:
Select DATALENGTH(c_problemText) as txtlen
from mytable
0
 
Anthony PerkinsCommented:
>>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
 
rw3adminCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
rw3adminCommented:
disregard my solution, Anthony (acperkins) is right,
0
 
Ike23Author Commented:
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
 
Ike23Author Commented:
I found the PostGRESQL function also.  It was just LENGTH().  Thanks!

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now