Display null when table column having comma separated value in SQL server

Question

0.00/5 (No votes)

See more:

SQL-Server-2014

I have one table with hire_date and rec_name column, where rec_name having comma separated value when I do filtration rec_name with multiple value shows as null but if it contain single value it will display.

What I have tried:

SQL

select * from indent_master

output

indnet_no  hire_date   status  rec_name
1          2019-13-09  On     Sonam,Meenakshi,Nalini
2          2019-12-09  on     Sonam

But when I write query

SQL

select * from indent_master
where date between '2019-09-01' and '2019-09-30'

output will be

indnet_no  hire_date   status  rec_name
1          2019-13-09  On     null
2          2019-12-09  on     Sonam

Posted 9-Oct-19 20:54pm

shwetavaidya30

Updated 9-Oct-19 23:39pm

Maciej Los

v2

Add a Solution

Comments

OriginalGriff 10-Oct-19 3:30am

Are you complaining that this does happen, or that it should happen?

shwetavaidya30 10-Oct-19 5:23am

i dont want null i want data must display as it is

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2019-10-09T21:48:00

Solution 1

Quote:
rec_name having comma separated value when I do filtration rec_name with multiple value shows as null but if it contain single value it will display

If i understand you well....

SQL

SELECT indnet_no,  hire_date,  status, CASE WHEN CHARINDEX(',', rec_name) > 0 THEN NULL ELSE rec_name END AS rec_name
FROM indent_master
where date between '2019-09-01' and '2019-09-30'

For further details, please see:
CHARINDEX (Transact-SQL) - SQL Server | Microsoft Docs[^]
CASE (Transact-SQL) - SQL Server | Microsoft Docs[^]

Posted 9-Oct-19 21:48pm

Maciej Los

Updated 9-Oct-19 23:37pm

v2

Comments

shwetavaidya30 10-Oct-19 5:27am

no I don't want null.

shwetavaidya30 10-Oct-19 5:27am

no I don't want null.

Maciej Los 10-Oct-19 5:38am

So, why you did provide description and example, which are different then the information in your comment?!?

OriginalGriff · Answer 2 · 2019-10-09T23:40:00

Quote:

OriginalGriff 2hrs ago
   Are you complaining that this does happen, or that it should happen?

shwetavaidya30 9 mins ago
   i dont want null i want data must display as it is

Then it's a problem with your data, not your query: unless you absolutely specify that SQL should change the data in some way (as Maciej Los suggested) it will always return exactly what it finds in the database that matches your query.

I'd start by looking at your condition and the column it is comparing against: your data examples don't make a lot of sense, since it would appear that the DB date is stored yyyy-dd-MM (a format I've never seen before and hope to never see again) while the query is passed as yyyy-MM-dd - a (sensible format). That might - for example - imply that your date column is actually a VARCHAR or NVARCHAR field, and that can give you some very silly looking results.

Sorry, but we can't do any of that for you - we have no access to your database!

(But ... I will say that storing CSV data in a DB is always a bad idea - it always creates problems later on and should be done with a separate table with a foreign key linking back to the original row.)

Display null when table column having comma separated value in SQL server

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Display null when table column having comma separated value in SQL server

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us