SQL tip for ProLaw Queries: LEN and DATALENGTH on different data types

Wednesday, April 10th, 2013 | ProLaw Case Management, Technical | Mike Smith

When queryuing various information from ProLaw through reports or the SQL Management Studio, its important to know which fields are TEXT, VARCHAR, or other forms of strings as the functions used to call the data can vary.

Addresses are a great example.  in ProLaw, the addresses are stored in both types of columns in SQL.  City, State, and ZIP are stored in varchar types, while ADDRLINES(the part that contains the street address) is stored in a text field.

LEN(varchar) = positive integer not including trailing spaces

DATALENGTH(varchar) = positive integer including spaces

LEN(text) = will not compute

DATALENGTH(text) = positive integer of atleast 1, and will be length of the data stored, included blank spaces at the end.

 

For example, the ADDRLINES field in the address table contains the entire address for a client.  If you wanted to strip off the city/state/zip from the end, you would need the lengths of each of the VARCHAR fields to use as the value to subtract.
If you are ever unsure, check the table structure in the ProLaw database.

 

Tags: , , , , , ,

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Page 1 of 11

Leave a Reply