Pattern selection - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Pattern selection

Author

Topic

dmilam
Posting Yak Master

185 Posts

Posted - 2011-02-21 : 14:44:22

I need to select a pattern within a string, but the pattern's length will vary.

Example, where "aaa" or "aa" is the pattern to be found in
"Xaaa11b" or "Xaa11b"; the neighboring characters will be constant.

Any way to dynamically select this, without having to add +1 or -1 to a variable?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-02-21 : 16:03:29

Since SQL does not accept a regex-like quantifier in replace/patindex/substring and other string functions, I can't think of an elegant way to do this. The only thing that comes to mind is something like this:

select case when
	replace(column_name,'a','') like '%X11b%' then 1 else 0 end from Your_Table;

That would match even if you had no a's in between the X and the 11b.

dmilam
Posting Yak Master

185 Posts

Posted - 2011-02-21 : 16:38:43

Thanks. Yes without regex it is difficult. I don't have access to any CLR solution.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-02-21 : 18:31:22

quote:
Originally posted by dmilam

Thanks. Yes without regex it is difficult. I don't have access to any CLR solution.

In ANSI/ISO Standard SQL and other products, we use the SIMILAR TO predicate which is based on the POSIX reg expression syntax.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

dmilam
Posting Yak Master

185 Posts

Posted - 2011-02-21 : 18:43:49

Not familiar with that predicate. I will look that up, thanks.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-02-24 : 03:54:02

If you want to know the exact pattern, use this

select data,substring(data,charindex('a',data),len(data)-charindex('a',reverse(data))) as pattern from
(
select 'Xaaa11b' as data union all
select 'Xaaa11b' as data union all
select 'Xaaaaaaa11b'
) as t

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources