format phone number in query

Posted by Dn2010 under ASP.NET on 4/18/2012 | Points: 10 | Views : 1568 | Status : [Member] | Replies : 6
hi friends,

i inserted like this
7032442030

but
i want insert src field in this format
eg
+1-703-244-2030

'+1-' +left( src , 3 )+ '-' + substring ( src , 4 , 3 )+ '-' + substring ( src , 7 , 4 ) as src

please modify the procedure like above format,


SET @mysqlQuery = 'SELECT calldate,src FROM mytable where src in (7032442029, 7032442030, 7032442031,7032442032) '
thanks.




Responses

Posted by: Rajni.Shekhar on: 4/18/2012 [Member] Bronze | Points: 25

Up
0
Down
You are at right direction, so what is your problem?
src field is varchar?

Thanks,
Rajni Shekhar

Dn2010, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Dn2010 on: 4/18/2012 [Member] Starter | Points: 25

Up
0
Down
i inserted like this
7032442030

but

i want to insert value like this,

eg
+1-703-244-2030

----------------------table-----------------------------
create table mytable(calldate varchar(200),phone varchar(300))


---------------------procedure-----------------------------



create procedure myproc

as

Begin

DECLARE @variable VARCHAR(50)

DECLARE @sqlQuery VARCHAR(8000)

DECLARE @finalQuery VARCHAR(8000)

Declare @LastUpdate_calldate varchar(200)

if isnull(@LastUpdate_calldate,'0')='0'

BEGIN
set @LastUpdate_calldate=replace(convert(varchar,getdate()-510,102),'.','-') + ' 00:00:00.000'

END

ELSE

BEGIN

set @LastUpdate_calldate=replace(convert(varchar,getdate(),102),'.','-') + ' 00:00:00.000'

END

Print @LastUpdate_calldate



SET @sqlQuery = 'SELECT DATE_FORMAT(calldate,''''%Y%-%m-%d %H:%I:%s.000'''') as calldate,src FROM mytable

where src in (7031442029, 7031442030,7031442069)
and
DATE_FORMAT(calldate,''''%Y%-%m-%d %H:%I:%s.000'''') > ' + '''' + '''' + @LastUpdate_calldate + '''' + ''''


SET @finalQuery = 'SELECT * FROM OPENQUERY(LNKMY,' + '''' + @sqlQuery + '' + ' Order By calldate desc ''' + ')'

SELECT @finalQuery


INSERT INTO MYTABLE (calldate,src)


EXEC(@finalQuery)

End

please modify this code,
thanks.


Dn2010, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rajni.Shekhar on: 4/18/2012 [Member] Bronze | Points: 25

Up
0
Down
create procedure myproc 


as

Begin

DECLARE @variable VARCHAR(50)

DECLARE @sqlQuery VARCHAR(8000)

DECLARE @finalQuery VARCHAR(8000)

Declare @LastUpdate_calldate varchar(200)

if isnull(@LastUpdate_calldate,'0')='0'

BEGIN
set @LastUpdate_calldate=replace(convert(varchar,getdate()-510,102),'.','-') + ' 00:00:00.000'

END

ELSE

BEGIN

set @LastUpdate_calldate=replace(convert(varchar,getdate(),102),'.','-') + ' 00:00:00.000'

END

Print @LastUpdate_calldate



SET @sqlQuery = 'SELECT DATE_FORMAT(calldate,''''%Y%-%m-%d %H:%I:%s.000'''') as calldate,(''''+1-'''' +left( src , 3 )+ ''''-'''' + substring ( src , 4 , 3 )+ ''''-'''' + substring ( src , 7 , 4 )) as phone FROM mytable

where src in (7031442029, 7031442030,7031442069)
and
DATE_FORMAT(calldate,''''%Y%-%m-%d %H:%I:%s.000'''') > ' + '''' + '''' + @LastUpdate_calldate + '''' + ''''


SET @finalQuery = 'SELECT * FROM OPENQUERY(LNKMY,' + '''' + @sqlQuery + '' + ' Order By calldate desc ''' + ')'

SELECT @finalQuery


INSERT INTO MYTABLE (calldate,src)


EXEC(@finalQuery)

End


Thanks,
Rajni Shekhar

Dn2010, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Dn2010 on: 4/18/2012 [Member] Starter | Points: 25

Up
0
Down
i run the sp but,

error happen

OLE DB provider "MSDASQL" for linked server "LNKVOICE" returned message "[MySQL][ODBC 3.51 Driver][mysqld-5.0.77]FUNCTION myvoicedb.substring does not exist".

please correct the error

Dn2010, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rajni.Shekhar on: 4/18/2012 [Member] Bronze | Points: 25

Up
0
Down
try to run it.

select ('+1-' +left( '7032442030' , 3 )+ '-' + substring( '7032442030' , 4 , 3 )+ '-' + substring ( '7032442030' , 7 , 4 )) as phone

What database you are using? if it is sql server, this syntax will run else you need to find out the substring function syntax of your database.

Thanks,
Rajni Shekhar

Dn2010, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Dn2010 on: 4/18/2012 [Member] Starter | Points: 25

Up
0
Down
i used IN keyword to src

where src in (7031442029, 7031442030,7031442069)

because
this query is not used
select ('+1-' +left( '7032442030' , 3 )+ '-' + substring( '7032442030' , 4 , 3 )+ '-' + substring ( '7032442030' , 7 , 4 ))


any changes in the line (MYSQL)

(''''+1-'''' +left( src , 3 )+ ''''-'''' + substring ( src , 4 , 3 )+ ''''-'''' + substring ( src , 7 , 4 )) as phone

please correct this,

or any changes to insert query that is SQL SERVER

please give the solution,

thanks.

Dn2010, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response