August 1, 2008 at 4:19 am
I have a table which contains the two column ID and skilltype.
Create table temp (ID Int identity(1,1),Skilltype varchar(1000))
go
INSERT INTO TEMP (Skilltype) select 'c, c++ (6),web development (6), java (3), unknown (21)'
INSERT INTO TEMP (Skilltype) select 'c# (15), design (12),windows (12), sql server (11), asp.net (11), web (11),soft (9),can (8) '
INSERT INTO TEMP (Skilltype) select 'jsp (9), testing (7), web (7), html (6),design (6), tomcat (6),javascript (5), servlets (5), cat,saarc, oracle'
The number inside the brackets is for our internal usage.The application developed requires that the if the user passes some values like c or c++, the query should exclude the the the row which contains c or c++.
select * from temp where skilltype not like '%c%'
But if I write like this it will also exclude the second and third row because it contains can and tomcat. How can I write a query where it only exclude the c?
August 1, 2008 at 4:29 am
You need to match the pattern at the beginning and end of the string, and in the middle, in different ways:
SET NOCOUNT ON
DROP table #Temp
CREATE TABLE #Temp (ID Int identity(1,1),Skilltype varchar(1000))
INSERT INTO #Temp (Skilltype) select 'c, c++ (6),web development (6), java (3), unknown (21)'
INSERT INTO #Temp (Skilltype) select 'c# (15), design (12),windows (12), sql server (11), asp.net (11), web (11),soft (9),can (8) '
INSERT INTO #Temp (Skilltype) select 'jsp (9), testing (7), web (7), html (6),design (6), tomcat (6),javascript (5), servlets (5), cat,saarc, oracle'
SELECT *
FROM #Temp
WHERE (UPPER(Skilltype) NOT LIKE 'C,%' -- at beginning of string
AND UPPER(Skilltype) NOT LIKE '% C,%' -- in the middle of the string
AND UPPER(Skilltype) NOT LIKE '% C %' -- last in the string
)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 1, 2008 at 4:32 am
Could you add a space and ( to the where clause.
Something like
not like 'C# (%' and not like '%,C# (%'
August 1, 2008 at 5:32 am
You should seriously consider modifying your table design so that it's more normalised. As a minimum, I would recommend creating a table like #t1 below. You can do this on-the-fly as I have done, but it would be much better to make this table permanent and update it whenever changes are made to your skilltypes table (or #Temp as we have here).
It will make your select queries so much easier and speedier.
--sample data
if object_id('tempdb..#Temp') is not null DROP table #Temp
go
CREATE TABLE #Temp (ID Int identity(1,1), SkillTypes varchar(1000))
INSERT INTO #Temp (SkillTypes) select 'c, c++ (6),web development (6), java (3), unknown (21)'
INSERT INTO #Temp (SkillTypes) select 'c# (15), design (12),windows (12), sql server (11), asp.net (11), web (11),soft (9),can (8) '
INSERT INTO #Temp (SkillTypes) select 'jsp (9), testing (7), web (7), html (6),design (6), tomcat (6),javascript (5), servlets (5), cat,saarc, oracle'
--parsing
if object_id('tempdb..#t1') is not null drop table #t1
go
; with
a as (select N from dbo.Tally where N <= 1000)
, b as (select * from #Temp cross join a where substring(',' + SkillTypes, N, 1) = ',')
, c as (select *, row_number() over (partition by ID order by ID) as row from b)
, d as (select c.Id, ltrim(substring(c.SkillTypes, c.N, isnull(d.N, 1000) - c.N - 1)) as SkillType
from c left outer join c d on c.ID = d.ID and c.row = d.row - 1)
, e as (select *, charindex('(', SkillType) as b1, charindex(')', SkillType) as b2 from d)
, f as (select Id,
ltrim(case when b1 > 0 and b2 > b1 then stuff(SkillType, b1, b2-b1+1, '')
else SkillType end) as SkillType,
ltrim(case when b1 > 0 and b2 > b1 then substring(SkillType, b1+1, b2-b1-1)
else null end) as Number
from e)
select * into #t1 from f
--queries
select * from #t1
select * from #Temp where id not in (select id from #t1 where SkillType = 'c')
/* results
Id SkillType
----------- ------------------
1 c
1 c++
1 web development
1 java
1 unknown
2 c#
2 design
2 windows
2 sql server
2 asp.net
2 web
2 soft
2 can
3 jsp
3 testing
3 web
3 html
3 design
3 tomcat
3 javascript
3 servlets
3 cat
3 saarc
3 oracle
ID SkillTypes
----------- -------------------------------------------------------------------------------------------------------------------
2 c# (15), design (12),windows (12), sql server (11), asp.net (11), web (11),soft (9),can (8)
3 jsp (9), testing (7), web (7), html (6),design (6), tomcat (6),javascript (5), servlets (5), cat,saarc, oracle
*/
The Tally table has many uses and can be found here: http://www.sqlservercentral.com/articles/TSQL/62867/
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
August 1, 2008 at 5:35 am
Hi
Use this SQL
select * from temp222 where ','+skilltype+',' not like '%,c,%'
Thanks
Vaseem
August 1, 2008 at 5:37 am
Sorry I written table temp222 instead of temp
select * from temp where ','+skilltype+',' not like '%,c,%'
August 1, 2008 at 5:38 am
vaseem (8/1/2008)
HiUse this SQL
select * from temp222 where ','+skilltype+',' not like '%,c,%'
Thanks
Vaseem
This looks interesting Vaseem, would you mind elaborating with some sample data?
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 1, 2008 at 5:43 am
Create table temp222 (ID Int identity(1,1),Skilltype varchar(1000))
go
INSERT INTO TEMP222 (Skilltype) select 'c, c++ (6),web development (6), java (3), unknown (21)'
INSERT INTO TEMP222 (Skilltype) select 'c# (15), design (12),windows (12), sql server (11), asp.net (11), web (11),soft (9),can (8) '
INSERT INTO TEMP222 (Skilltype) select 'jsp (9), testing (7), web (7), html (6),design (6), tomcat (6),javascript (5), servlets (5), cat,saarc, oracle'
Now your table will have data like this
ID SkillType
1c, c++ (6),web development (6), java (3), unknown (21)
2c# (15), design (12),windows (12), sql server (11), asp.net (11), web (11),soft (9),can (8)
3jsp (9), testing (7), web (7), html (6),design (6), tomcat (6),javascript (5), servlets (5), cat,saarc, oracle
and if you want to fetch the records which are not containing c then execute this sql
select * from temp222 where ','+skilltype+',' not like '%,c,%'
this will give u
ID SkillType
2c# (15), design (12),windows (12), sql server (11), asp.net (11), web (11),soft (9),can (8)
3jsp (9), testing (7), web (7), html (6),design (6), tomcat (6),javascript (5), servlets (5), cat,saarc, oracle
Thanks
Vaseem
August 1, 2008 at 5:49 am
Vaseem, try it with a little extra data...
INSERT INTO #Temp (Skilltype) select 'c, c++ (6),web development (6), java (3), unknown (21)'
INSERT INTO #Temp (Skilltype) select 'c# (15), design (12),windows (12), sql server (11), asp.net (11), web (11),soft (9),can (8) '
INSERT INTO #Temp (Skilltype) select 'jsp (9), testing (7), web (7), html (6),design (6), tomcat (6),javascript (5), servlets (5), cat,saarc, oracle'
INSERT INTO #Temp (Skilltype) select 'c# (15), design (12),windows (12), sql server (11), c, asp.net (11), web (11),soft (9),can (8) '
INSERT INTO #Temp (Skilltype) select 'c# (15), design (12),windows (12), sql server (11), C, asp.net (11), web (11),soft (9),can (8) '
INSERT INTO #Temp (Skilltype) select 'jsp (9), testing (7), web (7), html (6),design (6), tomcat (6),javascript (5), servlets (5), cat,saarc, oracle, c'
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 1, 2008 at 6:04 am
you have stuffed spaces thats why this sql is not working properly, but this is only the logic, it does not meant that it will work with all the possible data
you need to modify sql as per the requirement
for you extra data you can use this
select * from temp222 where replace(','+skilltype+',',' ','') not like '%,c,%'
August 1, 2008 at 6:26 am
Why use a simple solution when a complicated one works just as well? 😛
SET NOCOUNT ON
DROP TABLE #Temp
CREATE TABLE #Temp ([ID] Int identity(1,1),Skilltype varchar(1000))
INSERT INTO #Temp (Skilltype) select 'c, c++ (6),web development (6), java (3), unknown (21)'
INSERT INTO #Temp (Skilltype) select 'c# (15), design (12),windows (12), sql server (11), asp.net (11), web (11),soft (9),can (8) '
INSERT INTO #Temp (Skilltype) select 'jsp (9), testing (7), web (7), html (6),design (6), tomcat (6),javascript (5), servlets (5), cat,saarc, oracle'
INSERT INTO #Temp (Skilltype) select 'c# (15), design (12),windows (12), sql server (11), c, asp.net (11), web (11),soft (9),can (8) '
INSERT INTO #Temp (Skilltype) select 'c# (15), design (12),windows (12), sql server (11), C, asp.net (11), web (11),soft (9),can (8) '
INSERT INTO #Temp (Skilltype) select 'jsp (9), testing (7), web (7), html (6),design (6), tomcat (6),javascript (5), servlets (5), cat,saarc, oracle, c'
SELECT t.*
FROM #Temp t
LEFT JOIN (SELECT [ID]
FROM (SELECT [ID], [Skill] = RTRIM(LTRIM(SUBSTRING(Skilltype+',', number,
CHARINDEX(',', Skilltype+',', number) - number)))
FROM Numbers, #Temp
WHERE number <= LEN(REPLACE(Skilltype,' ','|'))
AND SUBSTRING(',' + Skilltype, number, LEN(REPLACE(',',' ','|'))) = ','
) d WHERE Skill = 'C' OR Skill = 'c'
) d2 ON d2.[ID] = t.[ID]
WHERE d2.[ID] IS NULL
ORDER BY t.[ID]
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 1, 2008 at 6:55 am
Chris Morris (8/1/2008)
Why use a simple solution when a complicated one works just as well? 😛
SET NOCOUNT ONDROP TABLE #Temp
CREATE TABLE #Temp ([ID] Int identity(1,1),Skilltype varchar(1000))
INSERT INTO #Temp (Skilltype) select 'c, c++ (6),web development (6), java (3), unknown (21)'
INSERT INTO #Temp (Skilltype) select 'c# (15), design (12),windows (12), sql server (11), asp.net (11), web (11),soft (9),can (8) '
INSERT INTO #Temp (Skilltype) select 'jsp (9), testing (7), web (7), html (6),design (6), tomcat (6),javascript (5), servlets (5), cat,saarc, oracle'
INSERT INTO #Temp (Skilltype) select 'c# (15), design (12),windows (12), sql server (11), c, asp.net (11), web (11),soft (9),can (8) '
INSERT INTO #Temp (Skilltype) select 'c# (15), design (12),windows (12), sql server (11), C, asp.net (11), web (11),soft (9),can (8) '
INSERT INTO #Temp (Skilltype) select 'jsp (9), testing (7), web (7), html (6),design (6), tomcat (6),javascript (5), servlets (5), cat,saarc, oracle, c'
SELECT t.*
FROM #Temp t
LEFT JOIN (SELECT [ID]
FROM (SELECT [ID], [Skill] = RTRIM(LTRIM(SUBSTRING(Skilltype+',', number,
CHARINDEX(',', Skilltype+',', number) - number)))
FROM Numbers, #Temp
WHERE number <= LEN(REPLACE(Skilltype,' ','|'))
AND SUBSTRING(',' + Skilltype, number, LEN(REPLACE(',',' ','|'))) = ','
) d WHERE Skill = 'C' OR Skill = 'c'
) d2 ON d2.[ID] = t.[ID]
WHERE d2.[ID] IS NULL
ORDER BY t.[ID]
You're ignoring the brackets. In which case, we can do this...
; with
a as (select id, cast('<x>' + replace(Skilltype, ',', '</x><x>') + '</x>' as xml) as xml from #Temp)
, b as (select a.*, rtrim(ltrim(c.value('(.)[1]', 'varchar(100)'))) as x from a cross apply xml.nodes('x') T(c))
select Id from b where x = 'c'
Edit: xml pasting issue resolved.
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
August 1, 2008 at 7:10 am
That's well smart, Ryan. Got a TSQL version? 😛
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 1, 2008 at 7:15 am
Chris Morris (8/1/2008)
That's well smart, Ryan. Got a TSQL version? 😛
Huh?
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
August 1, 2008 at 9:07 pm
As Ryan suggested, you should consider NOT storing delimited data in a single column...
If it's not something you can control, then Vaseem was on the right track. Even with the added extra spaces, the following will work...
--sample data
if object_id('tempdb..#Temp') is not null DROP table #Temp
go
CREATE TABLE #Temp (ID Int identity(1,1), SkillType varchar(1000))
INSERT INTO #Temp (Skilltype) select 'c, c++ (6),web development (6), java (3), unknown (21)'
INSERT INTO #Temp (Skilltype) select 'c# (15), design (12),windows (12), sql server (11), asp.net (11), web (11),soft (9),can (8) '
INSERT INTO #Temp (Skilltype) select 'jsp (9), testing (7), web (7), html (6),design (6), tomcat (6),javascript (5), servlets (5), cat,saarc, oracle'
INSERT INTO #Temp (Skilltype) select 'c# (15), design (12),windows (12), sql server (11), c, asp.net (11), web (11),soft (9),can (8) '
INSERT INTO #Temp (Skilltype) select 'c# (15), design (12),windows (12), sql server (11), C, asp.net (11), web (11),soft (9),can (8) '
INSERT INTO #Temp (Skilltype) select 'jsp (9), testing (7), web (7), html (6),design (6), tomcat (6),javascript (5), servlets (5), cat,saarc, oracle, c'
SELECT *
FROM #Temp
WHERE ','+REPLACE(SkillType,' ',',')+',' NOT LIKE '%,c,%'
AND ','+REPLACE(SkillType,' ',',')+',' NOT LIKE '%,c++,%'
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply