MSSQL数字拼接的简单套路（mssql 数字拼接）
Stitching Numbers Together In MSSQL
Stitching numbers together in MSSQL is a valuable technique – whether you’re making reports from financial data or are just looking to render a pattern or identifier from other values. There’s a few different methods you can use within SQL Server to quickly and effectively join pieces of information together. Here, we’ll go over them.
The simplest way to stitch numbers together is to start off with the basic CONCAT function. This does as advertised, connecting strings or any other type of values — like numbers — together in the order you specify. Being a general purpose string concatenation function makes it incredibly useful, too, as you can quickly join together multiple numbers and produce a longer value. Let’s look at an example to see how well it works.
SET @year = 2020
SET @month = 7
SET @day = 11
SELECT CONCAT(@year, @month, @day)
This query produces the result of “20200711” — achieving exactly what we specified without needing any extra input or functions. The same technique also works if you don’t have the exact variables handy, or wish to include additional formatting information. For example, we might want to add a hyphen after the month and day so we have “2020-07-11”. We can do this by simply modifying our query a bit to put the data in the correct format:
SELECT CONCAT(@year, ‘-‘, @month, ‘-‘, @day)
This yields the expected result of “2020-07-11”. We can also format the numbers to fit any specific needs we might have. For example, if we wanted to add a leading 0 to each individual number, we could do so with a few extra formatting parameters:
SELECT CONCAT(FORMAT(@year, ‘0000’), ‘-‘, FORMAT(@month, ’00’), ‘-‘, FORMAT (@day, ’00’))
Doing this renders the number as “2020-07-11” — exactly what we wanted.
In addition to these functions, the FORMAT() function can also help you stitch numbers together in different ways. This is especially useful if you need to combine numbers into a longer value that contains letters. For example, if you have 3 separate numbers that you need to combine into a 13 digit code, it’s possible to do this using FORMAT, as we can see here:
SELECT FORMAT(YEAR1, ‘0000’) + FORMAT(MONTH1, ’00’) + FORMAT(DAY1, ’00’) + FORMAT(YEAR2, ’00’) + FORMAT(MONTH2, ’00’) + FORMAT(DAY2, ‘000’)
This query returns a value like “202007115600” — and in this way, you can quickly and easily join numbers together to form a specific pattern or code.
These are just a few examples of the many different ways that you can join numbers together in MSSQL. By utilizing the functions and syntax provided by SQL Server, you can create custom formulas that combine numbers into just about any form of value you can imagine – and with a little bit of practice, you may find that it isn’t as difficult as you think.
