CODEWITHSUNDEEP
sql-server
andy
andy

Reputation: 6079

SQL Query Behaviour

I have a column called 'CLIENTDATA' in table CLIENTDET and have a data like 'last-ST - Tranactions - Challan Details - Int. Calc. - Interest Report - N'.

Case 1:

When i write a query 

SELECT CLIENTDATA,
       LEN(CLIENTDATA)
FROM   CLIENTDET

In this case i am getting correct data and length as '75'.

Case 2:

When i write a query 

SELECT CAST(
           CASE 
                WHEN CLIENTDATA IS NULL THEN ''
                ELSE CLIENTDATA
           END AS 
           VARCHAR
       ),
       LEN(
           CAST(
               CASE 
                    WHEN CLIENTDATA IS NULL THEN ''
                    ELSE CLIENTDATA
               END 
               AS VARCHAR
           )
       )
FROM CLIENTDET

In this case i am not getting correct data as in Case 1, here length is '30'.

Anything i am missing here or not understood?.... Image is attached

enter image description here

Upvotes: 0

Views: 79

Answers (1)

bummi
bummi

Reputation: 27367

When you cast a literal to character type without specifying the length,
by default it is 30. So you need to specify the enough column length when you use character datatypes. Ref.
and here msdn reference

e.g.

Declare @a varchar(100)
Select @a='123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890'

Select LEN(CAST(@a as Varchar)),LEN(@a)

as additonal information

When declaring a VARCHAR variable without defining the length, it will default to 1.

DECLARE @a VARCHAR
SET @a = 'ABCDE'
SELECT @a

Returns: "1"

Upvotes: 3

Related Questions