SQL Server Portal

Google translate provides API to translate text from one language to another. In this article, we will use this API in SQL Server and translate some text. Please sign up in google translate and create an account which provides you the Key.
Please use this Key in the given below stored procedure.

Before creating the stored procedure, you need to run given below script to enable Ole Automation Procedures. This is a prerequisite for the given below solution.

Enable OLE Automation procedures:

 
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

Solution:

 

USE tempdb
GO

CREATE OR ALTER PROCEDURE sp_translate_text
@TextToBeTranslated NVARCHAR(500),
@SourceLanguage NVARCHAR(50),
@TargetLanguage NVARCHAR(50),
@Key NVARCHAR(250) = 'Please use Translate API key provided by google'  

AS

DECLARE @URL NVARCHAR(250) = 'https://translation.googleapis.com/language/translate/v2';
DECLARE @ConsolidatedURL NVARCHAR(250) = CONCAT(@URL,'?key=',@Key); 
DECLARE @Object INT;
DECLARE @Json TABLE(DATA NVARCHAR(MAX));
DECLARE @ResponseText NVARCHAR(MAX);
DECLARE @Body NVARCHAR(MAX)  = CONCAT('{','"q":','"',@TextToBeTranslated,'"',',','"target":','"',@TargetLanguage,'"',',','"source":','"',@SourceLanguage,'"','}');

EXEC sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
EXEC sp_OAMethod @Object, 'OPEN', NULL, 'POST', @ConsolidatedURL, 'FALSE';
EXEC sp_OAMethod @Object, 'SETREQUESTHEADER', NULL, 'CONTENT-TYPE', 'APPLICATION/JSON';
EXEC sp_OAMethod @Object, 'SEND', NULL, @Body;

EXEC sp_OAMethod @Object, 'RESPONSETEXT', @ResponseText OUTPUT , @Json OUTPUT;
INSERT INTO @Json exec sp_OAGetProperty @Object, 'RESPONSETEXT';

DECLARE @Data NVARCHAR(MAX) = (SELECT DATA FROM @Json);

SELECT @SourceLanguage AS [Source Language]
	 , @TargetLanguage AS [Target Language]
	 , TranslatedText  AS [Translated Text]
FROM OPENJSON(@Data,'$.data.translations')
WITH
(
 [TranslatedText] NVARCHAR(500) '$.translatedText'  
);
GO

• Example 1:

Text to be translated: “How are you”

Source Language: English

Target Language: Urdu

 

USE tempdb
GO

EXEC sp_translate_text N'how are you','en','ur';
GO

• Example 2:

Text to be translated: “آپ کیسے ہو”

Source Language: Urdu

Target Language: English

 

USE tempdb
GO

EXEC sp_translate_text N'آپ کیسے ہو','ur','en';
GO

• Example 3:

Text to be translated: “How are you”

Source Language: English

Target Language: Arabic

 

USE tempdb
GO

EXEC sp_translate_text N'how are you','en','ar';
GO

• Example 4:

Text to be translated: “كيف حالكم”

Source Language: Arabic

Target Language: English

 

USE tempdb
GO

EXEC sp_translate_text N'كيف حالكم','ar','en';
GO

• Example 5:

Text to be translated: “How are you”

Source Language: English

Target Language: Hindi

 

USE tempdb
GO

EXEC sp_translate_text N'how are you','en','hi';
GO

• Example 6:

Text to be translated: “आप कैसे हैं”

Source Language: Hindi

Target Language: English

 

USE tempdb
GO

EXEC sp_translate_text N'आप कैसे हैं','hi','en';
GO

Conclusion:

I found Google translate API very useful whenever I need to convert any text from one language to another. It works very smoothly. Do let me know if you use it and how did you find it.

Read Full Post »