Madhivanan

Parsing a string

Now-a-days lot of questions are asked in the forum about extracting particular text from a string

Here I give some examples on how to do it

--Example 1 (Extracting tags and other informations)

declare @s varchar(100)

declare @temp table(id int identity(1,1),data varchar(100))

set @s='567<a>data<fg>ua<li>test<iu>welcome'

select @s=replace(replace(@s,'<','~!@#'),'>','~!@#')

while charindex('~!@#',@s)>0  
begin
        insert into @temp(data)
        select substring(@s,1,charindex('~!@#',@s)-1)
        set @s=substring(@s,charindex('~!@#',@s)+4,len(@s))
end

insert into @temp(data)
select @s

--Extract the tags
select data from @temp where id%2=0

--Example 2 (Extract URLs)

declare @s varchar(1000)

set @s='"ramkumar<a href="www.yahoo.com" mce_href="www.yahoo.com">hhh</a>mahesh<a title href="www.gmail.com" mce_href="www.gmail.com" />hhjjkjhsdf< as href="test.test.com" mce_href="test.test.com"'

--Example 3 (Extract phone numbers with the format ddd-ddd-dddd where d denotes a number)

select 
        data,
        substring(data,patindex('%[0-9][0-9][0-9][-][0-9][0-9][0-9][-][0-9][0-9][0-9][0-9]%',data),12) as ph_no
from
(
        select 'kashdk hasfjh sjkfhk h123-456-8765 kjsdkfjj h093 kljsdf903354512' as data union all
        select 'khasd ksfjh 345-245-9871kljk 90234 99jkxc 902304809n kljsdkfj9034'
) as t
where 
        patindex('%[0-9][0-9][0-9][-][0-9][0-9][0-9][-][0-9][0-9][0-9][0-9]%',data)>0

select fruit,substring(amount,1,patindex('%[0-9][ ]%',amount+' ')) from
(
select 
        Fruit, 
        substring(fruit,charindex('$',fruit),len(fruit)) as amount
        from 
        (
                select '9 Lemons cost 67 $99.99 on sale' as fruit union all
                select '$5.99 Apples 877 are on sale' union all
                select 'Where are the $65.99 lemons 7856' union all
                select 'Oranges costs $99.5'
        )as Pricing
) as t

Also refer these
http://sqlblogcasts.com/blogs/madhivanan/archive/2009/10/12/removing-tags-from-a-string.aspx
http://sqlblogcasts.com/blogs/madhivanan/archive/2009/08/03/regular-expression.aspx

Usage of Semicolons in SQL Server

As you know, semicolon is used as a statement terminator in almost all RDBMSs. However the usage of semicolon is optional in SQL Server except a few cases. Here are atleast the three cases where usage of semicolon is a must

1 At the start of the Common Table Expression (If any statement preceeds it)

2 If the procedure name is GO ,to execute it use at the end of GO (ex EXEC GO; as shown in Fun with GO)

3 At the end of Merge statement

SQL Server 2008 features that work only in Enterprise Edition

One of the new features available in SQL Server 2008 is compressed backup which takes backup of a database with mimimum possible size. The syntax is

backup database db_name to disk='backup_path'
with init, compression

But this works only in Enterprise Edition of the SQL Server 2008 and not in other editions

If you want to know the new features that only work in Enterprise Edition,refer this
http://msdn.microsoft.com/en-us/library/cc645993.aspx