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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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.)