• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 371
  • Last Modified:

How to display only specific values from the multiple value column.

I have a Data Table column named code.It can have values like ;1;2;3;4;5;6;7;8;9;..... upto 50
I am trying to get the records only if they have 5,6,7and 8 codes in the column.

But I don't want to display all other values the code column can have.

Ex:I don't want to display like this 2,4,5,8,20,34,45,46.Instead I want to display 5,8.

The column has to  show only the values mentioned in the where clause if it has.

Thanks
0
KavyaVS
Asked:
KavyaVS
  • 3
  • 3
  • 2
  • +1
4 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
something like this:
select youcolumn
, case when yourcolumn like '%;5;%' then '5,' else '' end
+ case when yourcolumn like '%;8;%' then '8,' else '' end
from yourtable
where yourcolumn like '%;5;%'
   or yourcolumn like '%;8;%'

Open in new window

0
 
NetExpert_plCommented:
You can use CHARINDEX function to check if the column contains ';5', ';6', ';7' or ';8'. Use SUBSTRING and CONCAT to build a column for display.
0
 
KavyaVSAuthor Commented:
Can you please give me a example how to use CHARINDEX function and using SUBSTRING and CONCAT to build a column for display.

Thanks.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
NetExpert_plCommented:
CHARINDEX(';5', code) will return a position of ';5' if found or 0 otherwise.

You can use it in a condition for a SELECT to choose only rows containing values you want:

... WHERE CHARINDEX(';5', code) OR CHARINDEX(';6', code) OR CHARINDEX(';7', code) OR CHARINDEX(';8', code)

SUBSTRING will cut values you want to return:

SUBSTRING(code, CHARINDEX(';5', code), 2) will return ';5' if 5 is in the code column.

CONCAT will glue all the substrings together. The final clause could be:

SELECT 
  CONCAT(
    SUBSTRING(code, CHARINDEX(';5', code), 2), 
    SUBSTRING(code, CHARINDEX(';6', code), 2), 
    SUBSTRING(code, CHARINDEX(';7', code), 2), 
    SUBSTRING(code, CHARINDEX(';8', code), 2)
  )
...

Open in new window


You cannot do above since it will always take two first characters if one of the digits is not found. So use CASE with it.

See functions definitions:
http://msdn.microsoft.com/en-us/library/ms186323.aspx
http://msdn.microsoft.com/en-us/library/ms187748.aspx
http://msdn.microsoft.com/en-us/library/hh231515
0
 
Scott PletcherSenior DBACommented:
SELECT
    --...other_columns...,
    SUBSTRING(
        CASE WHEN code LIKE '%;5;%' THEN ',5' ELSE '' END +
        CASE WHEN code LIKE '%;6;%' THEN ',6' ELSE '' END +
        CASE WHEN code LIKE '%;7;%' THEN ',7' ELSE '' END +
        CASE WHEN code LIKE '%;8;%' THEN ',8' ELSE '' END
        , 2, 8) AS Matching_Codes
FROM ( --dbo.yourTable
    SELECT '2;4;5;8;20;34;45;46' AS code UNION ALL
    SELECT ';1;2;3;4;5;6;7;8;9;.....'
) AS test_data
WHERE
    code LIKE '%;[5678];%'
0
 
NetExpert_plCommented:
SELECT
    CONCAT(
        CASE WHEN code LIKE '%;5;%' THEN ',5' ELSE '' END,
        CASE WHEN code LIKE '%;6;%' THEN ',6' ELSE '' END,
        CASE WHEN code LIKE '%;7;%' THEN ',7' ELSE '' END,
        CASE WHEN code LIKE '%;8;%' THEN ',8' ELSE '' END
     ) AS Matching_Codes
FROM Data
WHERE
    code LIKE '%;[5678];%' 

Open in new window

0
 
KavyaVSAuthor Commented:
Hi,
'CONCAT' is not a recognized built-in function name.
I am using sql 2008 R2.
Please let me know how to make it work in sql 2008 R2.


I tried another one.I used only this part.
SELECT
    --...other_columns...,
    SUBSTRING(
        CASE WHEN code LIKE '%;5;%' THEN ',5' ELSE '' END +
        CASE WHEN code LIKE '%;6;%' THEN ',6' ELSE '' END +
        CASE WHEN code LIKE '%;7;%' THEN ',7' ELSE '' END +
        CASE WHEN code LIKE '%;8;%' THEN ',8' ELSE '' END
        , 2, 8) AS Matching_Codes
FROM dbo.yourTable
WHERE   code LIKE '%;[5678];%'   --what 2,8 doing.
   
I didn't use this part.
    FROM ( --dbo.yourTable
    SELECT '2;4;5;8;20;34;45;46' AS code UNION ALL
    SELECT ';1;2;3;4;5;6;7;8;9;.....'
) AS test_data

Thanks
0
 
Scott PletcherSenior DBACommented:
The "2, 8" are used just to strip off the leading comma (,).  Since we don't know which value(s) will be found, we must prefix all of them with a comma, so the original final result looks like this:

,5,6,7,8

Using SUBSTRING(',5,6,7,8', 2, 8) converts that to:

5,6,7,8

which looks much better.

The "FROM (...) AS test_data" was just sample data to show test results.  Replace all that with:

FROM dbo.yourTableName

to run against your real table.
0
 
KavyaVSAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now