I could use Derived Table instead CTE.

I see first_value and last_value function, but do not see second or third value function!

How can I write for example second_value() function by using analytical window function?

ScottPletcher (1/24/2013)

declare @search_pattern_should_be varchar(100)

set @search_pattern_should_be = '%[[][[][%] SQL Server [%]]]%'

--example

select case when @string like '%[[][[][%] SQL Server [%]]]%'

then'Matched' else...

Thanks,

So the second one can be more simpler like this:

LIKE '$[$[$% SQL Server $%]]' ESCAPE '$'

It's easy no need to sample data, I want an alternative LIKE for this without ESCAPE.

like '%?[?[?% SQL Server ?%?]?]%' ESCAPE '?'

I wrote this code, but that is not correct:

like...

I forgot to say thank you.

The generalizability is necessary.

Because your solution return duplicate combos! you need to filter them.

For example see ComboNo IN (1, 11,...

I think your result is not correct. Run you query with following sample data:

INSERT INTO #n_objects

SELECT 1, 'Apple' UNION ALL

SELECT 2, 'Orange' UNION ALL...

My...

declare @t table(id int, string varchar(500))

insert @t values (1,'abcABC,0123456789~!@'),(2,'123abcdefghijklmnopqrstuvwzyz');

with cte1 as

(

select id,

0 nbr,

cast(string as varchar(500))...

declare @t table(id int, string varchar(500))

insert @t values (1,'abc0123456789'),(2,'123abcdefghijklmnopqrstuvwzyz');

with cte as

(

select id,1 nbr, cast(string as varchar(500)) string

from @t

union all

...

where RecordId = 3212

and

(

'2012' > ANY

(select AlphaNumeric2 union all

select AlphaNumeric5 union all

...

Thank you Sean Lange.

😀

select m.RecordId,m.NumberForSearch into #dump

from table1 m join table2 a on m.RecordId = a.RecordId

cross apply (

select 1

from (

values(a.AlphaNumeric2),

(a.AlphaNumeric5),

...

where RecordId = 3212

and not(replace(AlphaNumeric2 + AlphaNumeric5 + AlphaNumeric7 + AlphaNumeric9, '2012', '') = '')

Only in some case also:

;with cte as

(

select m.RecordId,m.NumberForSearch,

a.AlphaNumeric2,a.AlphaNumeric5,a.AlphaNumeric7,a.AlphaNumeric9

from table1 m join table2 a on m.RecordId = a.RecordId

where RecordId = 3212

)

select RecordId,NumberForSearch into...

What about this:

where RecordId = 3212

and 1 in

(select case when AlphaNumeric2 = '2012' then 0 else 1 end union

select case when AlphaNumeric5 =...