You make an interesting point, Steve. I too have studied the martial arts on and off for most of my life spending a few of those years in the dojo,...

Try...

Declare @i Int,@Str VarChar(max);Set @Str=' '+Char(9)+'a1b2c3'+char(0)+'?5.'

Print '|'+@Str+'|';Print Len(@Str);

Select @i=PatIndex('%[^0-9]%',@Str)

While @i>0 Select @Str=Stuff(@Str,@i,1,''),@i=PatIndex('%[^0-9]%',@Str)

Print '|'+@Str+'|';Print Len(@Str);

Wow, some of these threads live a longer life than the merits of their content would seem to dictate. I posted his article to show how a new feature could...

Executing within a TRY/CATCH block won't affect performance. However, the setup and tear down of the construct does have its overhead. Run the following:

Use TempDB;

Declare @i Int,@st DateTime,@a Int;

Select @i=100000,@a=0,@st=GetDate()

While...

The use of a CTE or the following alternative will only perform well on large tables if you have fairly specific index on the ordered column. Also, you could:

Select *...

and... CRAP :angry:

while running Jeff's examples I discovered torn pages in my master database...

Msg 824, Level 24, State 2, Line 12

SQL Server detected a logical consistency-based I/O error: torn page...

I do agree... at the end of the day performance generally decides the issue. Its just that even a simple language like TSQL allows for a suprising number of ways...

Yes, every technique has its achilles heel... one of the CTE's is the recursion level...

But this works great for most things I used to use WHILE loops or TALLY tables...

And here's an OCCURS function using a CTE...

Declare @Str VarChar(max);

Select @Str='This is a test...';

With Occurs as

(

Select SubString(@Str,1,1)[Chr],1[Idx]

Union All

Select SubString(@Str,Idx+1,1),Idx+1

from Occurs

where Idx<Len(@Str)

)

Select Count(*) from Occurs where Chr='t' Option (MaxRecursion 32767)

Ups, had to changed a couple of things I missed when I constructed this from a couple of different functions...

Ok... no WHILE loop, no TALLY table...

Declare @OldStr VarChar(max),@NewStr VarChar(max);

Select @NewStr='',@OldStr='ab123c';

With NumOnly as

(

Select Case when SubString(@OldStr,1,1) like '[0-9]' then SubString(@OldStr,1,1) else '' End[Chr],1[Idx]

Union All

Select Case when SubString(@OldStr,Idx+1,1) like '[0-9]' then...

Use sp_OAxxx and COM object "Scripting.FileSystemObject":)

I tried something similar with sp_OAxxx and OLE object "MSXML2.ServerXMLHTTP". In spite of MS's insistance to have removed the size limitations in SQL2005 of VarChar, nVarChar,... etc. you will still...

Source is the name of a TinyInt column in the table.

Source&4<>0 is a method of determining a binary bit's value, in this case the 3rd bit from the right.

Source&4 is...

Our current database represents data source as a TinyInt column containing bit values to indicate source type; 1=Source1, 2=Source2, 4=Source3, etc... where the decimal numbers 1, 2, & 4 represent...