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

Ved gravatar image

Ved
52 4 5 7

(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

Fatherjack gravatar image

Fatherjack ♦♦
42.4k 75 78 108

+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 :)

<!-- Begin Edit

I can edit my comments, just not for too long. I think that 1 hour is the limit, so yes, it is too late to edit the select top 1 1 r thingy.

--> 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/][1]

[1]: 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

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 20 23 32

@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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x1945
x985
x4

asked: Aug 19, 2010 at 06:04 AM

Seen: 1992 times

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