Last Friday i was working on our one of legacy application. And we were required for finding length from text field but when i tried below sql..
select top 2 circuit_id,len(prov_config) from config
sql server given error
Msg 8116, Level 16, State 1, Line 4
Argument data type text is invalid for argument 1 of len function.
When I tried to convert into varchar then result was completely different than actual.
select top 2 circuit_id,len(cast(prov_config as varchar)),prov_config from config
Finally I tried Datalength which I got from SQLAuthority.
select top 2 circuit_id,Datalength(prov_config ),prov_config from config
I Solved my problem using Datalength but the question still running in my mind why there is two function for finding Length LEN for char and DATALENGTH for Text and why the errors comes Argument data type text is invalid for argument 1 of len function. .
Thanks for reading