Shulou(Shulou.com)05/31 Report--

SQL in how to achieve zero data operation, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

For example, for integers 88 and 800, when 800 is greater than 88 as a number type, there is no doubt that 800 is greater than 88, but when converted into a string, the size of '800' and' 88' is subverted. Let's do a small experiment and treat it as a verification. The script is as follows:

Declare @ num1 int=88; declare @ num2 int=800; if cast (@ num1 as varchar) > cast (@ num2 as varchar) print''88dollars' is greater than '800dollars', 'else print'', '800dollars' is greater than '88dollars'

The running results are as follows:

In fact, it is easy to understand that this is because the varchar size comparison starts from the * * bit, comparing the ascii code size, when * is larger than the other, then the size of the entire varchar is determined.

Based on this logic, when we convert int to varchar, we may have to sort or merge with other fields. If the size or order is out of order, it may affect our operation. How should we deal with this situation?

In fact, it is very simple, we can fix the number of bits after conversion, and if there are not enough digits, we can fill in zeros on the left. For example, we fixed 7 digits, "88" corresponds to "0000088" and "800,800" corresponds to "0000800". At this time, if we compare or sort again, there will be no chaos.

So how to make up for zero? The reason is very simple. We first use a string of six zeros plus a string converted by a number, and then use the right function to intercept the rightmost seven bits. See the following script:

Right ('000000'+cast (@ num1 as varchar), 7)

This method is the simplest. If you don't like it, you can also calculate the length after int is converted to varchar, and then create the missing zeros according to the length and replicate function. See the following script:

Replicate (cast (@ num1 as varchar)) + cast (@ num1 as varchar)

Obviously, it seems that * methods are more intuitive. If you are not satisfied, you can also use the case statement to determine that different lengths directly fill in different digits of zeros, as shown in the following script:

Case len (cast (@ num1 as varchar)) when 1 then '000000'+cast (@ num1 as varchar) when 2 then' 00000'+cast (@ num1 as varchar) when 3 then '0000'+cast (@ num1 as varchar) when 4 then' 000'+cast (@ num1 as varchar) when 5 then '00'+cast (@ num1 as varchar) when 6 then' 0'+cast (@ num1 as varchar) when 7 then cast (@ num1 as varchar) end

This is obviously a little bit done, I believe that few people will do so, but this method is also feasible.

For the effects of various processing methods, please see the following figure:

By the same token, if you want to fill in zeros on the right, the same is true. Here you use the left function, for example:

Left (cast (@ num1 as varchar) + '0000001)

It's just filling in the back zero, intercepting it from the left.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.