0

I have a table with candidate details which insert base64 image as the profile picture. When I getting huge number of candidate list from the table the profile picture is taking too much space. I have a default base64 image as profile pic for those doesn't upload profile pics. What I want is take null if the profile picture is DEFAULT for the candidate. My Query is something simple like this,

select 
    Name,
    DOB, 
    case when profilepic is default then NULL else profile pic 
from candidates

The code is showing error for using default but works fine when I use null in place of DEFAULT

Improve this question
9
  • IS DEFAULT isn't valid syntax. What are you actually trying to achieve here?
    – Thom A
    Commented Jun 30, 2021 at 10:16
  • Also your CASE expression is missing an END.
    – Thom A
    Commented Jun 30, 2021 at 10:17
  • getting null value with the candidate if the profilepic is default value in the table or else get the profile picture
    – user15959877
    Commented Jun 30, 2021 at 10:18
  • Not easy. You'll need to inspect the CONSTRAINT in the sys objects, see if the value matches, and then return NULL instead.
    – Thom A
    Commented Jun 30, 2021 at 10:19
  • 3
    You can set to null all the default pic (less space used on DB). Then, in your code, if the value is null you load the default pic.
    – Emanuele
    Commented Jun 30, 2021 at 10:23

2 Answers 2

Reset to default
1

Something along the lines of:

select 
Name
,DOB
,case when profilepic = (SELECT COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS  WHERE COLUMN_NAME='profilepic' and TABLE_NAME='candidates') then NULL else profilepic END 
from candidates

You might need to parse the suqbuery a bit.

Improve this answer
4
  • will this take much time to complete ?
    – user15959877
    Commented Jun 30, 2021 at 10:28
  • YMMV, depends on how much data you have in you DB. Check the explain plan beforehand or check a subset of data first using TOP.
    – Gabriel Durac
    Commented Jun 30, 2021 at 10:29
  • @KERALAZEUZ Can't imagine it would make much difference, the subquery is not correlated so it will probably be pushed through as a single outer reference. You could always assign it to a variable first if you're worried. Note that this is not going to work if the default is anything other than a literal value, and even if it is, you still need to parse the SQL out of the constraint
    – Charlieface
    Commented Jun 30, 2021 at 10:55
  • @KERALAZEUZ . . . You have to be careful with this approach. The default value is surfaced as a string in the information_schema view, which can cause subtle issues when compared to data stored as other types. That says, you don't have much choice.
    – Gordon Linoff
    Commented Jun 30, 2021 at 10:59
0

Should be more efficient like this :

SELECT Name, DOB,
       CASE WHEN profilepic = T.COLUMN_DEFAULT 
                THEN NULL 
            ELSE profilepic 
       END 
FROM   candidates
       CROSS APPLY (SELECT COLUMN_DEFAULT 
                    FROM   INFORMATION_SCHEMA.COLUMNS  
                    WHERE  COLUMN_NAME = 'profilepic' and 
                           TABLE_NAME = 'candidates' and
                           TABLE_SCHEMA = 'dbo' ) AS T

Assuming that your table is in SQL Server database default schema (dbo).

Improve this answer