x

Extract 'AA' or 'BB' type pattern using SQL Server

Problem Statement:The table is like this

 Emp_ID Name
 1      Aakash
 2      Aashish
 3      Varun
 4      Vikas
 5      Jiignesh

I have to extract the Names which have alphabets consecutively e.g. Aakash has AA Aashish has AA Jiignesh has ii

and reject other names ........Please help me out with this

more ▼

asked Aug 19, 2010 at 06:04 AM in Default

avatar image

Ved
52 5 5 8

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

firstly create a table to contain all of the combinations you want to locate

 CREATE TABLE DoubleLetters ( combo CHAR(2) PRIMARY KEY )
   GO
 INSERT  INTO DoubleLetters
 VALUES  ( 'aa' ),
         ( 'bb' ),
         ( 'cc' ),
         ( 'dd' ),
         ( 'ee' ),
         ( 'ff' ),
         ( 'gg' ),
         ( 'hh' ),
         ( 'ii' ),
         ( 'jj' ),
         ( 'kk' ),
         ( 'll' ),
         ( 'mm' ),
         ( 'nn' ),
         ( 'oo' ),
         ( 'pp' ),
         ( 'qq' ),
         ( 'rr' ),
         ( 'ss' ),
         ( 'tt' ),
         ( 'uu' ),
         ( 'vv' ),
         ( 'ww' ),
         ( 'xx' ),
         ( 'yy' ),
         ( 'zz' )
   go

then write a function to return a result when it finds a match

 CREATE FUNCTION ufn_HasDoubleLetter ( @thename CHAR(50) )
 RETURNS TABLE
 AS return
     ( SELECT    case when patindex('%' + combo + '%', @thename) > 0
                      THEN @thename
                 END AS r
       FROM      doubleletters
     )
   go

then use it

 SELECT  [n].[empID] ,
 [n].[FName] 
 FROM    [dbo].[NAMES] AS n
         CROSS APPLY [dbo].[ufn_HasDoubleLetter]([n].[FName]) AS c
 WHERE   [c].[r] IS NOT null

to give results like this

 empID   FName    
 1        Aakash    
 2        Aashish    
 5        Jiignesh    

more ▼

answered Aug 19, 2010 at 07:03 AM

avatar image

Fatherjack ♦♦
43.7k 79 98 117

+1- maybe want to add a group by to avoid duplicates of names with two doubles? Like aaron aaronson? :)

Aug 19, 2010 at 07:32 AM Matt Whitfield ♦♦

@Matt, good call. Thats something that would come after you review the results. although this query isnt going to find Fname and SName combos as its only passing the FName col. Point taken though.

Aug 19, 2010 at 07:41 AM Fatherjack ♦♦

@Fatherjack @Matt Whitfield +1 This is a very elegant solution! The problem with multi-duplicates within same name can be cured by restricting the TVF to only return one record (now it always returns 26 per each record in the table), i.e.

;with doubleletters(combo)
as
(
select top 26
    replicate(char(96 + 
        row_number() over (order by [object_id])), 2) combo
    from sys.objects
)
    select e.*
        from dbo.names e cross apply
        (
            select top 1 1 r
                from doubleletters 
                    where patindex('%' + combo + '%', e.name) > 0
        ) tvf;
Aug 19, 2010 at 08:17 AM Oleg

@Oleg - liking this a lot, why dont you post it as an answer?

Aug 19, 2010 at 09:01 AM Fatherjack ♦♦

@Fatherjack Because that would be stealing :) I really like your answer, very elegant one indeed. All I did was just a restatement of your answer so it fits in the comment box. Now when I look at my comment I realize that select top 1 1 r looks somewhat ridiculous, maybe I should consider changing statements like this to something more readable, like select top 1 'X' as r :)

End Edit

Aug 19, 2010 at 09:16 AM Oleg
(comments are locked)
10|1200 characters needed characters left

Oleg's modification to Fatherjack's answer is probably the best way to go, and I applaud them both for their elegance.

But for completeness you might also want to consider writing a CLR assembly in C#/VB.NET and importing it into SQL Server. This would permit you to use RegEx in your solution and avoid the need to create a table. Phil Factor has a fantastic article on doing this at http://www.simple-talk.com/sql/t-sql-programming/clr-assembly-regex-functions-for-sql-server-by-example/

more ▼

answered Aug 19, 2010 at 10:08 AM

avatar image

TimothyAWiseman
15.6k 22 51 38

@TimothyAWiseman Wow, this is a really great article! Thank you. Then again, I don't believe that it is possible to find any article by Phil Factor which is less than a must read.

Aug 19, 2010 at 10:26 AM Oleg

Some of his less technical articles vary in how interesting they are, but all of the technical articles are fantastic.

Aug 19, 2010 at 12:48 PM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2018
x1067
x5

asked: Aug 19, 2010 at 06:04 AM

Seen: 2333 times

Last Updated: Aug 19, 2010 at 06:13 AM

Copyright 2016 Redgate Software. Privacy Policy