Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2006-10-23
6
Medium Priority
?
584 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 2000 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
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!

 
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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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 ?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

604 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