Feeds:
Posts
Comments

Posts Tagged ‘T-SQL’

SQL Server – Fix – The second and third arguments of the TRANSLATE built-in function must contain an equal number of characters.

Posted in Errors, SQL SERVER, tagged , , , , , on November 14, 2022| Leave a Comment »

TRANSLATE() is one of the handy functions of SQL Server when it comes to replace one or more characters to another set of characters in one go. I have earlier written an article about it in detail.

Recently, I was using TRANSLATE() function and came across an error as mentioned below.

Error :

The second and third arguments of the TRANSLATE built-in function must contain an equal number of characters.


DECLARE @Number AS VARCHAR(15)
SET @Number='+92-3317892345'
SELECT @Number AS PhoneNumber
     , TRANSLATE(@Number,'+','00') AS TranslatedPhoneNumber;

GO
--OUTPUT

Solution:

As per SQL Server documentation translations must be the same data type and length as characters. And if you look at our script, the length of both characters (+) and translation (00) is not same.

In such cases, we need to simply apply REPLACE() function since TRANSLATE() function cannot handle such cases as shown below.


DECLARE @Number AS VARCHAR(15)
SET @Number='+92-3317892345'
SELECT @Number AS PhoneNumber
     , REPLACE(@Number,'+','00') AS REPLACEDPhoneNumber;
GO
--OUTPUT

Conclusion:

In case of this error, we should use REPLACE() function instead of TRANSLATE() function.

Read Full Post »

SQL Server 2012 – List of all sequences available in the Database

Posted in Solutions, SQL SERVER, tagged , , , , , , on October 24, 2012| Leave a Comment »

SQL Server 2012 came up with multiple good features. One of them is sequence. I have discussed this feature in my earlier artcile.

In this article, we will discuss how to find list of all sequences along with  its current value & other different properties in the database. So, we can achieve this with the help of system view namely sys.sequences.

Given below are the list of properties, we need to find in all sequences in the database.

  • Sequence Name
  • object ID
  • Creation Date
  • Last Modified Date
  • Schema
  • Numeric Precision
  • Numeric Scale
  • Start Value
  • Increment Valye
  • Min Value
  • Max Value
  • Is Cycle Enabled
  • Sequence Cache Type
  • Cache Size
  • Current Value
SELECT
seq.name AS [Sequence Name],
seq.object_id AS [Object ID],
seq.create_date AS [Creation Date],
seq.modify_date AS [Last Modified Date],
SCHEMA_NAME(seq.schema_id) AS [Schema],
CAST(seq.precision AS int) AS [Numeric Precision],
CAST(seq.scale AS int) AS [Numeric Scale],
ISNULL(seq.start_value,N'''') AS [Start Value],
ISNULL(seq.increment,N'''') AS [Increment Value],
ISNULL(seq.minimum_value,N'''') AS [Min Value],
ISNULL(seq.maximum_value,N'''') AS [Max Value],
CAST(seq.is_cycling AS bit) AS [Is Cycle Enabled],
ISNULL(seq.cache_size,0) AS [Cache Size],
ISNULL(seq.current_value,N'''') AS [Current Value]
FROM
sys.sequences AS seq

Read Full Post »