单双号限行，今天是否绿色出行，双号出行

单双号限行，今天是否绿色出行，双号出行

要判断车牌是否符合单双号出行。

这样得获取车牌最后一位数字是什么？

 

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Insus.NET -- Blog: https://insus.cnblogs.com -- Create date: 2019-06-02 -- Update date: 2019-06-02 -- Description: 返回字符中最后一位数字 -- ============================================= CREATE FUNCTION [dbo].[svf_LicensePlateTailNumber] ( @number VARCHAR(8) ) RETURNS INT AS BEGIN DECLARE @rtn_num INT IF LEN(ISNULL(@number,'')) > 0 BEGIN DECLARE @l INT = LEN(TRIM(LTRIM(@number))) WHILE @l >= 0 BEGIN DECLARE @char CHAR(1) = SUBSTRING(@number,@L,1) IF @char LIKE '%[0-9]%' BEGIN SET @rtn_num = CAST(@char AS INT) BREAK; END SET @l = @l - 1 END END RETURN @rtn_num END GO Source Code

 

再写另外一个函数，判断数字是否为偶数：

《判断数字是否为奇数或偶数》https://www.cnblogs.com/insus/p/10965118.html

 

现在，我们准备一些车牌，省略了前缀：

 

CREATE TABLE [dbo].[plate_number] ( [Number] VARCHAR(8) ) GO INSERT INTO [dbo].[plate_number] ([Number]) VALUES ('E124J'), ('S19J3'), ('88888'), ('55555'), ('2312H'), ('WE1AB') GO Source Code

 

准备好函数与数据，那我们开始进行查询：

 

;WITH cte_a AS ( SELECT [Number],[dbo].[svf_LicensePlateTailNumber]([Number]) AS [尾号] FROM [dbo].[plate_number] ) , cte_b AS ( SELECT [Number], [尾号],CASE WHEN [dbo].[svf_IsEven]([尾号]) = 1 THEN N'双号' ELSE N'单号' END AS [单双号] , CASE WHEN [dbo].[svf_IsEven]([尾号]) = [dbo].[svf_IsEven](DAY(CURRENT_TIMESTAMP)) THEN '通行' ELSE N'限行' END AS [是否可通行] FROM cte_a ) SELECT [Number],[尾号],[单双号],[是否可通行] FROM cte_b Source Code

 

运行得到的结果，测试的日期为2019-06-02：