June 3, 2016 at 4:51 am
I'm new to SQL and having come back from a training course, I'm trying to apply what I learnt against our data for all the course modules:
One of the simple things I looked at was filtering - and I did a very specific search for customers that had an O' in their name following by any character, then it had to be either 1 or 2 vowels, then an R, and then anything else - quite specific, but more of a test to see if I could do it.
So, I've fabricated a test to so what I mean (using the etiquette guide):
[font="Courier New"]--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--===== Create the test table with
CREATE TABLE #mytable
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, --Is an IDENTITY column on real table
CustomerName NVARCHAR(30),
)
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #mytable ON
--===== Insert the test data into the test table
INSERT INTO #mytable
(ID, CustomerName)
SELECT '1','O''Hare and Co' UNION ALL
SELECT '2','O''Leary Brothers Ltd' UNION ALL
SELECT '3','O''Flarety and Son' UNION ALL
SELECT '4','O''Brian' UNION ALL
SELECT '5','O''Rourke' UNION ALL
SELECT '6','O''Hara'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #mytable OFF
--this will find rows 1 & 6
select *
from #mytable as c
where c.CustomerName like 'o''_[aeiou]r%'
go
--this will find rows 2 & 5
select *
from #mytable as c
where c.CustomerName like 'o''_[aeiou][aeiou]r%'
go
--this will find rows 1, 2, 5 & 6
select *
from #mytable as c
where c.CustomerName like 'o''_[aeiou][aeiou]r%' or c.CustomerName like 'o''_[aeiou]r%'
go
[/font]
So the 3rd select is what I was expecting, but is there any way I can condense the where clause?
Thanks.
June 3, 2016 at 5:09 am
I think that's about as concise as you're going to get it. As you'll have noticed, string manipulation isn't one of T-SQL's strengths. If you're going to be doing this sort of thing a lot, you might consider using regular expressions, for which you'd need to write your own CLR (or download one from codeplex or similar).
John
June 3, 2016 at 5:22 am
John Mitchell-245523 (6/3/2016)
I think that's about as concise as you're going to get it. As you'll have noticed, string manipulation isn't one of T-SQL's strengths. If you're going to be doing this sort of thing a lot, you might consider using regular expressions, for which you'd need to write your own CLR (or download one from codeplex or similar).John
+1.
And for some one new to SQL your code looks neat!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply