How do I get specific data from the table

Question

5.00/5 (1 vote)

See more:

Hi All,
I am using Microsoft SQL Server 2014.
I need data say only for sector 7 i.e. 'Test7' but I am getting data for 'Test6' also.

create table test_data1(Name varchar(100),sector varchar(100))

insert into test_data1 values('Test1','22,23,24')
insert into test_data1 values('Test2','2')
insert into test_data1 values('Test3','2')
insert into test_data1 values('Test4','2,3,4,9')
insert into test_data1 values('Test5','26')
insert into test_data1 values('Test6','13,14,27')
insert into test_data1 values('Test7','5,6,7')

What I have tried:

select * from test_data1

Name	sector
Test1	22,23,24
Test2	2
Test3	2
Test4	2,3,4,9
Test5	26
Test6	13,14,27
Test7	5,6,7

select * from test_data1 where sector like '%7' or sector like '%,7' or sector like '7,%' or sector like '%,7,%'

Name	sector
Test6	13,14,27
Test7	5,6,7

Although this query is working properly for sector 2 and sector 22 where all data related to sector 2 and 22 is getting displayed properly.

select * from test_data1 where sector like '%2' or sector like '%,2' or sector like '2,%' or sector like '%,2,%'

Name	sector
Test2	2
Test3	2
Test4	2,3,4,9

select * from test_data1 where sector like '%22' or sector like '%,22' or sector like '22,%' or sector like '%,22,%'

Name	sector
Test1	22,23,24

Can anybody please help me to get data only for sector 7 i.e. data in the following manner?

Name	sector
Test7	5,6,7

Posted 6-Jan-20 20:54pm

Member 14708983

Updated 6-Jan-20 21:50pm

Add a Solution

3 solutions

Solution 1

For starters, don't store your data like that. Using comma delimited data inside a column is a PITA for all processing, no matter how good an idea ti may have seems when you got started. Yes, it's easy to insert, but after that it becomes a total nightmare to deal with as SQL has very limited string handling facilities.

To check what you have is a good idea, think about a simple change: Alter Text4 to replace Sector 4 with Sector 5. Easy to do?

Instead, use two tables:
TestDatas

ID      INT, IDENTITY (Or UNIQUEIDENTIFIER, your preference
Name    NVARCHAR

Sectors

ID      INT, IDENTITY
Sector  INT
TDID    INT, FOREIGN KEY to TextDatas (Or UNIQUEIDENTIFIER, FOREIGN KEY to TextDatas)

Then you can use a simple JOIN to combine the tables and DELETE, UPDATE, and SELECT operations become relatively trivial.

Posted 6-Jan-20 21:06pm

OriginalGriff

Comments

Member 14708983 7-Jan-20 4:37am

Thanks for the quick response. But the input data in the sector field will be comma separated only. e.g. 2,3,4,9 or 1,4,7,22,26

OriginalGriff 7-Jan-20 5:52am

Yes. And it's still a bad idea to store it like that.
Split it up in your presentation software and store it in a sensible manner - it makes your life a whole load easier later on.

Member 14708983 7-Jan-20 7:12am

Thanks a lot for your kind help. It's definitely a good idea. Thanks once again.

OriginalGriff 7-Jan-20 7:18am

You're welcome!

Solution 2

Solution 1 is the correct approach, however so that you can see what you did wrong, try replacing

SQL

select * from test_data1 where sector like '%7' or sector like '%,7' or sector like '7,%' or sector like '%,7,%'

with

SQL

select * from #test_data1 where sector like '%,7' or sector like '7,%' or sector like '%,7,%'

Essentially you need to check that "7" is the last part of the column text, the first part of the column text or somewhere in the middle - that's why the commas are there.

By checking for '%7' you were checking twice for column text ending with "7" - any number ending in 7

Posted 6-Jan-20 21:34pm

CHill60

Comments

Member 14708983 7-Jan-20 4:42am

Thanks for the quick response. After using query provided by you, I am getting proper data for sector 7, but if I use the same query for sector 2, it is displaying just 1 record, Ideally it should show 3 records.

select * from test_data1 where sector like '%,2' or sector like '2,%' or sector like '%,2,%'

Name sector
Test4 2,3,4,9

--My Query
select * from test_data1 where sector like '%2' or sector like '%,2' or sector like '2,%' or sector like '%,2,%'

Name sector
Test2 2
Test3 2
Test4 2,3,4,9

CHill60 7-Jan-20 6:28am

As per @Eek-Ten-Bears in Solution 3 - I also missed the trivial case of

or sector = 2

However, I was not offering a solution per se - Solution 1 is the correct way forward and solution 3 offers a way of splitting the data into the necessary table.

I was only trying to point out why your query didn't work - I see however that you are still including where sector like '%2' which is wrong!

Member 14708983 7-Jan-20 7:11am

Thanks a lot for your kind help. I have corrected it...

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)

Eek Ten Bears · Accepted Answer · 2020-01-06T21:50:00

I agree entirely with OriginalGriff

You may have a reason for building your data in that form but are they really valid? If so You have a myriad of "outs" and two jump out at me.

1. Read up on PATINDEX the code below will find single instances of 7 anywhere in your string, it won't find 77, 17, 73 or anything else.

select * from #test_data1 where PATINDEX('%[^0123456789]7[^0123456789]%', sector) + PATINDEX('%[^0123456789]7', sector) + PATINDEX('7[^0123456789]%', sector)> 0

This is clunky and it will miss for example you putting in 07 instead of 7 or 7.0 or probably 100 other things. It also isn't repeatable.

2. Create a split string function such as this one of mine, I tend to use it when processing text coming in from imported data so I can split into two tables as per OriginalGriff's wisdom, but you could use it for your application. Again it depends on your strings being quite well behaved but it means you get a nice table like entity to play with as an output, it also adds in line numbers for sorting which helps. NOTE THIS IS NOT PERFECT I DON'T NEED IT TO BE SO USE WITH CARE

ALTER FUNCTION [yourSchema].[SplitString_RowNumbered] (
      @InputString                  nVARCHAR(4000),
      @Delimiter                    VARCHAR(50)
)

RETURNS @Strings TABLE (
      StringRow                             int,
      StringElement                         nVARCHAR(4000)
)
-- adjustment 14/05/2019  delimiters of more than 1 character included the delimiter characters beyond the first character
AS
BEGIN
      IF @Delimiter = ' '
      BEGIN
            SET @Delimiter = ','
            SET @InputString = REPLACE(@InputString, ' ', @Delimiter)
      END

      IF (@Delimiter IS NULL OR @Delimiter = '')
            SET @Delimiter = ','


      DECLARE @Element        nVARCHAR(4000);
      DECLARE @Elements       nVARCHAR(4000);
      DECLARE @DelimIndex     INT;
      DECLARE @RowNumber      INT = 0;

      SET @Elements = @InputString
      SET @DelimIndex = CHARINDEX(@Delimiter, @Elements, 0)
      WHILE (@DelimIndex != 0)
      BEGIN
            SET @Element = SUBSTRING(@Elements, 0, @DelimIndex);  -- get the first element
            SET @RowNumber += 1;                                  -- increment the row
            INSERT INTO @Strings VALUES (@RowNumber, @Element)

            -- Set @ItemList = @ItemList minus one less item
            SET @Elements = SUBSTRING(@Elements, @DelimIndex+len(@Delimiter + 'x') - 1, LEN(@Elements)-@DelimIndex)
            SET @DelimIndex = CHARINDEX(@Delimiter, @Elements, 0)
      END -- End WHILE

      -- have one segment left
      INSERT INTO @Strings VALUES (@RowNumber + 1, @Elements) -- if no deliminators were found @Elements is the @InputString otherwise its the rightnost segment



      RETURN

END -- End Function