question

Chris 2 avatar image
Chris 2 asked

A hardcoded field was truncated

Hi, Windows 2003 and SQL 2005, a SQL something like: SELECT 'InvalidProfile-Mapping' AS Name, CASE WHEN ProfileName IS NULL THEN 'InvalidProfile-Mapping' ELSE ProfileName END AS ProfileName FROM [dbo].[TableA] WITH(NOLOCK) It retuns: Name ProfileName InvalidProfile-Mapping InvalidProfile somewhere the second won't take/show any string after InvalidProfile. Thanks, Chris
sql-server-2005t-sql
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image Kev Riley ♦♦ commented ·
As @Scot shows this is due to the datatype of `ProfileName` being varchar(14)
1 Like 1 ·
Chris 2 avatar image Chris 2 commented ·
Hi kev, Thanks for replying. I tried: SELECT COALESCE(ColName, 'InvalidProfile-Mapping') FROM Table but it did not work. When the field is null, it only shows 'InvalidProfile'. One thing I noticed is that the colname's lenghth is 14 so is the length of 'InvalidProfile-Mapping'. Chris
0 Likes 0 ·

1 Answer

·
Scot Hauder avatar image
Scot Hauder answered
The only way I can reproduce what you are experiencing is using ISNULL. In this case one solution is to use COALESCE instead of ISNULL DECLARE @TableA TABLE(UserID int, [ProfileName] varchar(14)) INSERT @TableA VALUES(1,'A'),(1,NULL) SELECT 'InvalidProfile-Mapping' AS Name, ISNULL(ProfileName,'InvalidProfile-Mapping') AS ProfileName FROM @TableA
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.