CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2005
Pavan Kumar
Pavan Kumar

Reputation: 463

Concatenate 2 numbers in SQL Server

I'm in need of a function to concatenate 2 numbers in SQL Server

eg

getuserid(3,333) = 3333
getuserid(8,5) = 8005
getuserid(2,11) = 2011

It should return the result based on the length of second parameter (like 3rd example)

I tried converting it to string and using case statements I concatenated it. But, I feel that,this can be done mathematically in a more effective manner. Can anyone please suggest a way.?

By the by, the output returned should be a numeric type.!

Upvotes: 2

Views: 7911

Answers (4)

DareDevil
DareDevil

Reputation: 5349

I have little modified above solution provided by @Devrat, because I want to just concatenate values rather adding 000 between, and if the second number is 0 it will return only first number

Create FUNCTION dbo.concat_num (@a INT, @b INT)
RETURNS INT
AS BEGIN
  IF @b = 0 
   BEGIN
 RETURN @a
   END  
  ELSE
   BEGIN
 RETURN CONVERT(INT, CONVERT(VARCHAR(2), @a) + CONVERT(VARCHAR(2), @b))
   END
  RETURN 0

END

Upvotes: 1

Mudassir Hasan
Mudassir Hasan

Reputation: 28781

As mentioned in your question

....It should return the result based on the length of second parameter.....

That means probably you meant

getuserid(3,333) = 3333
getuserid(8,5) = 85
getuserid(2,11) = 211

I have used LEN() on second parameter and Power() function on 10

CREATE FUNCTION dbo.concat_num (@a INT, @b INT)
RETURNS INT
AS BEGIN

  RETURN  @a * POWER(10,LEN(@b)) + @b

END

Upvotes: 2

Bohemian
Bohemian

Reputation: 425448

Looks like this will do it:

select num1 * 1000 + num2

Why do you need a function for this simple math?

Upvotes: 8

Devart
Devart

Reputation: 122040

Try this one -

CREATE FUNCTION dbo.concat_num (@a INT, @b INT)
RETURNS INT
AS BEGIN

  RETURN CONVERT(INT, CONVERT(VARCHAR(25), @a) + REPLICATE('0', CASE WHEN LEN(@b) > 2 THEN LEN(@b) ELSE 3 END - LEN(@b)) + CONVERT(VARCHAR(25), @b))

END

Output:

-----------
3333
-----------
8005
-----------
2011
-----------
15555

Upvotes: 2

Related Questions