CODEWITHSUNDEEP
sql-servert-sql
sirrocco
sirrocco

Reputation: 8055

varchar field length reported incorrectly

I have a varchar(800) column that is a primary key in one table and a FK to another.

The problem is that if I do len(field) - it says 186. If I copy/paste the text and I check it in notepad or something, I have 198 characters

The content is this :

http://news.google.com/news/url?sa=t&fd=R&usg=AFQjCNGGTo8JmCWDydNA19MrL4aON-02pA&url=http://creativity-online.com/news/chrysler-nokia-target-among-winners-of-teds-first-ad-contest/149189

Any ideas on why the length difference?

EDIT

You are right. I was using a web based sql manager and that tricked me.

Thank you.

Upvotes: 0

Views: 324

Answers (3)

gbn
gbn

Reputation: 432180

186:

http://news.google.com/news/url?sa=t&fd=R&usg=AFQjCNGGTo8JmCWDydNA19MrL4aON-02pA&url=http://creativity-online.com/news/chrysler-nokia-target-among-winners-of-teds-first-ad-contest/149189

198:

http://news.google.com/news/url?sa=t&fd=R&usg=AFQjCNGGTo8JmCWDydNA19MrL4aON-02pA&url=http://creativity-online.com/news/chrysler-nokia-target-among-winners-of-teds-first-ad-contest/149189

Note the & and &: 3 of them, & is 4 characters longer = 12

You are neither comparing equally nor comparing the same strings.

In SQL:

SELECT
  LEN('http://news.google.com/news/url?sa=t&fd=R&usg=AFQjCNGGTo8JmCWDydNA19MrL4aON-02pA&url=http://creativity-online.com/news/chrysler-nokia-target-among-winners-of-teds-first-ad-contest/149189'),
    LEN('http://news.google.com/news/url?sa=t&fd=R&usg=AFQjCNGGTo8JmCWDydNA19MrL4aON-02pA&url=http://creativity-online.com/news/chrysler-nokia-target-among-winners-of-teds-first-ad-contest/149189')

Upvotes: 0

Guffa
Guffa

Reputation: 700152

Are you HTML encoding the URL after you have read it from the database?

moriartyn suggested that the SQL Server len function would count & as a single character, but that is not the case. However, if the actual content in the field is not HTML encoded, and it's HTML encoded when inserted in the page, that would change each & character into &, which would account for the extra length.

Upvotes: 3

nickmoriarty
nickmoriarty

Reputation: 1263

My guess is that because there are three & in your text, the sql server len function is counting those as just & or one character, and in notepad it is counting them as five each, that would give you twelve extra in that count.

Upvotes: 2

Related Questions