Alternatives · Basic Logics · Real Scenario Logic's

TRIM Functionality for INT data typed values in SQL – Issue with Solution

Introduction

In this blog, we will see what is the issue when we use TRIM function in SQL for INT (Integer) data typed values and what is the solution for it.

Issue – ‘Argument data type int is invalid for argument 1 of Trim function.’

Sample SQL Script

DECLARE @A VARCHAR(10),@B INT;
SET @A='   54  ';
SET @B=@a;
SELECT @B; 
SELECT TRIM(@B);

In the above code, While converting Varchar to Integer data type , Integer datatype automatically trims the leading and trailing spaces. Additionally if we use TRIM function to INT data typed values , then the error comes as ‘Argument data type int is invalid for argument 1 of Trim function.‘.

Solution!

SQL Script

DECLARE @A VARCHAR(10),@B INT;
SET @A='   54  ';
SET @B=@a;
SELECT @B; 
SELECT RTRIM(LTRIM(@B));

In the above code, We used LTRIM and RTRIM functions to avoid the error that TRIM function gives us before. Although, if we check the datatype of the result set that LTRIM and RTRIM function gives, we would find that it changed the datatype as VARCHAR instead of INT. This is because, the TRIM, LTRIM and RTRIM functions gives the result sets in Varchar Datatype.

If we run the below script, we can find the datatype changes that LTRIM/RTRIM does-

IF OBJECT_ID('TEMPDB..#TEMP') IS NOT NULL
DROP TABLE #TEMP;

DECLARE @A VARCHAR(10),@B INT;
SET @A='   54  ';
SET @B=@a;
SELECT @B AS B,RTRIM(LTRIM(@B)) AS B1 INTO #TEMP;

SELECT * FROM #TEMP;

EXEC TEMPDB..SP_HELP #TEMP;

So if we use CAST or CONVERT to change the datatype back to INT, then the final solution looks like below-

Final SQL Script

IF OBJECT_ID('TEMPDB..#TEMP') IS NOT NULL
DROP TABLE #TEMP;

DECLARE @A VARCHAR(10),@B INT;
SET @A='   54  ';
SET @B=@a;
SELECT @B AS B,CAST(RTRIM(LTRIM(@B)) AS INT) AS B1 INTO #TEMP;

SELECT * FROM #TEMP;

EXEC TEMPDB..SP_HELP #TEMP;

Conclusion

Thus, we saw the issue while using TRIM function in SQL for INT (Integer) data typed values and the solution to overcome it.This blog post is mainly written to know the issue and the solution to use TRIM functionality in SQL for INT (Integer) data typed values, even though Integer Datatype doesn’t need TRIM function. Here, we have used LTRIM and RTRIM instead of TRIM Function and also used CAST Function to achieve TRIM functionality in SQL for INT (Integer) data typed values.

Thanks for reading!

FOLLOW BLOG for interesting upcoming posts!

Advertisement

2 thoughts on “TRIM Functionality for INT data typed values in SQL – Issue with Solution

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s