Click here to Skip to main content
14,427,368 members
Rate this:
Please Sign up or sign in to 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
Updated 6-Jan-20 22:50pm
Rate this:
Please Sign up or sign in to vote.

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.
   
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!
Rate this:
Please Sign up or sign in to vote.

Solution 2

Solution 1 is the correct approach, however so that you can see what you did wrong, try replacing
select * from test_data1 where sector like '%7' or sector like '%,7' or sector like '7,%' or sector like '%,7,%'
with
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
   
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...
Rate this:
Please Sign up or sign in to vote.

Solution 3

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
   
v2
Comments
Member 14708983 7-Jan-20 4:46am
   
Thanks for the quick response. Here also I am getting proper solution by using your query for sector 7 data, but when I use the same query for getting sector 2, it is displaying just 1 records. I am expecting 3 records as shown in my original post.

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

Name sector
Test7 5,6,7

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

Name sector
Test4 2,3,4,9
Eek Ten Bears 7-Jan-20 6:06am
   
DUH my bad, I forgot the trivial case - either add an "AND sector = 2" or do this which is prettier but includes a pointless application of PATINDEX
where PATINDEX('%[^0123456789]2[^0123456789]%', sector) + PATINDEX('%[^0123456789]2', sector) + PATINDEX('2[^0123456789]%', sector) + PATINDEX('2', sector)> 0
Member 14708983 7-Jan-20 7:10am
   
Thanks a lot dear.
PATINDEX solution worked. I tested data for various inputs and everything seems to be working perfectly fine.

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
Test8 2,14,15
Test9 3,14,5
Test10 8,9
Test11 2,14
Test12 12,14

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

Name sector
Test2 2
Test3 2
Test4 2,3,4,9
Test8 2,14,15
Test11 2,14

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

Name sector
Test5 26

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

Name sector
Test10 8,9

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100