T-SQL: Get number from string

When you are working with custom software over your database, you can easily find some data you need to clean up. It can be trash from poorly written code, or users can write rubbish if no controls are established. So next couple of posts will be about searching and updating this kind of trash values.
First is getting a number from a string.
So, I had a task of exporting phone numbers in unified format, but numbers were in all variety:
+(385)11 1111-111
0038511/1111-111
etc.
I used to write WHILE loop which would read a character by character in string, and then get the number in numeric string.
Then I found out that there are couple of functions PATINDEX and STUFF which can help me out quickly.
This idea cam from this site SQLAuthoritiy from Pinal Dave.

PATINDEX(‘%pattern%’,expression) – returns first position of pattern
image
STUFF(expression,start,length,replace_string) – replaces length in expression from position start with replace string
image

Now, when we got this clear, create a function which will return only string. Function is created on database level. So if you want to use it everywhere you must have permission and use it like this: database.schema.function

use database
go

CREATE FUNCTION fnGetNumberFromString
(@strInput VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @intNumber int
SET @intNumber = PATINDEX(‘%[^0-9]%’, @strInput) – set first location of number
BEGIN
WHILE @intNumber > 0 –until position number of number in string is null or 0
BEGIN
SET @strInput = STUFF(@strInput, @intNumber, 1, ” ) –replace string with ‘blank’
SET @intNumber = PATINDEX(‘%[^0-9]%’, @strInput )–next position of number
END
END
RETURN ISNULL(@strInput,0)
END
GO

Now just to test it

image 

That’s how you get numbers from string.

Good luck

Mail merge from SQL Database mail

Few months ago, I was given a task to send some notifications via circular letter. The best thing is to send it by MS WORD.
image
Unfortunately, after sending this, the attachment could not be read on the recipients side. Me and my colleague could not understand, is it because WORD or attachment or in the end mail server. Because we where on tight schedule, I came up with idea to send circular letter through SQL Database mail.
After importing the table of recipients, I have created an simple WHILE loop to call sp_send_dbmail.

It can be sent in plain text or HTML.
For this I have create a three users me, John Doe, Jane Doe.

This is T-SQL for loop plain text
/*****************************************PLAIN TEXT********************************************************/
use msdb
SET QUOTED_IDENTIFIER ON
GO

declare @Title varchar(20)
declare @Name varchar (200)
declare @email varchar(50)
declare @option varchar (600)
declare @attachment varchar(100)
declare @start int =(select min(id) from test.dbo.circular)
declare @end int =(select max(id) from test.dbo.circular)
declare @bodyrun varchar(2048)

while @start<=@end
begin

set @title=(select title from test.dbo.circular where id=@start)
set @Name=(select name from test.dbo.circular where id=@start)
set @email=(select email from test.dbo.circular where id=@start)
set @option=ISNULL(‘You have also selected an option "’+(select [option] from test.dbo.circular where id=@start)+’"’,”)
set @bodyrun =
”+@Title+’
‘+@Name+’

We must inform you that you have signed a contract with TESTFirm.

‘+@option+’

Best Regards

Somebody’

exec sp_send_dbmail
@profile_name =  ‘SarumanMails’,
@recipients = @email,
@from_address =  ‘sql@sql.com’ ,
@reply_to =  ‘sql@sql.com’,
@subject =’Contract and options’ ,
@body =  @bodyrun,
–@body_format=’HTML’,
@file_attachments =N’c:\test\contract.pdf’ –this is on server side

set @start=@start+1
end
/*****************************************PLAIN TEXT********************************************************/

/*****************************************HTMLTEXT********************************************************/

use msdb
SET QUOTED_IDENTIFIER ON
GO

declare @Title varchar(20)
declare @Name varchar (200)
declare @email varchar(50)
declare @option varchar (600)
declare @attachment varchar(100)
declare @start int =(select min(id) from test.dbo.circular)
declare @end int =(select max(id) from test.dbo.circular)
declare @bodyrun varchar(2048)

while @start<=@end
begin

set @title=(select title from test.dbo.circular where id=@start)
set @Name=(select name from test.dbo.circular where id=@start)
set @email=(select email from test.dbo.circular where id=@start)
set @option=ISNULL(‘You have also selected an option "’+(select [option] from test.dbo.circular where id=@start)+’"’,”)
set @bodyrun =
‘<font size="3" face="Arial" color="black">
<p><big>’+@Title+'</p>
<p>’+@Name+'</big></p>
<p> </p>
<p> </p>
<p>We must inform you that you have signed a contract with TESTFirm.</p>
<p> </p>
<p>’+@option+'</p>
<BR>&nbsp;<BR>
<p></p>
<p>Best Regards</p>
<p></p>
<p>Somebody</p> </font>’

exec sp_send_dbmail
@profile_name =  ‘SarumanMails’,
@recipients = @email,
@from_address =  ‘sql@sql.com’ ,
@reply_to =  ‘sql@sql.com’,
@subject =’Contract and options’ ,
@body =  @bodyrun,
@body_format=’HTML’,
@file_attachments =N’c:\test\contract.pdf’ –this is on server side

set @start=@start+1
end
/*****************************************HTMLTEXT********************************************************/

I received three mails:
image

The result of plain text mail:
image 

The result of HTML mail:
image

So, that’s about it.

Good Luck