How to check if last 4 digits of value is in a range in SQL Server

sql substring right of character
how to get last 2 digits of a number in sql
sql select everything to the right of a character
sql server substring
sql substring left of character
sql left function in where clause
sql select last character of a string
sql server charindex

I have a column where each value is 9 characters (col1). I need to pull the last 4 characters of col1 and see if the value is in the range of 0001 - 0879. There could be letters in this string.

Initial table

col1       column2
585550529  red
589960MNO  blue
849828415  green

Expected result

col1       column2  column3
585550529  red      True
589960MNO  blue     False
849828415  green    False

SELECT col1, 
       col2, 
       CASE WHEN RIGHT(col1,4) IN('0001','0002','0003') THEN 'true' ELSE 'false' END
FROM table

Is there anyway to do this without having to type all 879 strings within the IN statement? This is going to get ugly.

You can use BETWEEN

...
CASE WHEN RIGHT(col1,4) between  '0001' and '0879' THEN 'true' ELSE 'false' END
...

SQL Server RIGHT() Function, W3Schools is optimized for learning, testing, and training. Examples might be simplified to improve reading and basic understanding. Tutorials, references, and​  Summary: in this tutorial, you will learn how to use the SQL Server LAST_VALUE() function to get the last value in an ordered partition of a result set. SQL Server LAST_VALUE() function overview The LAST_VALUE() function is a window function that returns the last value in an ordered partition of a result set.

What about:

SELECT col1, 
       col2, 
       CASE WHEN 
         isnumeric(RIGHT(col1,4)) and 
         cast(RIGHT(col1,4) as int) between 0 and 879
           THEN 'true' 
           ELSE 'false' 
       END
FROM table

RIGHT (Transact-SQL), If integer_expression is type bigint and contains a large value, A: Using RIGHT with a column characters of the first name for each person in the AdventureWorks2012 database. The following example returns the five rightmost characters of each last name in the DimEmployee table. View on GitHub. **@range_last_value** is sql_variant with the same base type as that of the sequence object used in the request. [ @range_cycle_count = ] range_cycle_count Optional output parameter returns the number of times that the sequence object cycled in order to return the requested range.

Try this Using PATINDEX

;WITH CTE(col1,column2)
AS
(
SELECT '585550529','red' UNION ALL
SELECT '589960MNO','blue' UNION ALL
SELECT '849828415','green'
)
SELECT col1,column2,CASE WHEN  newcol BETWEEN 1 AND  0879 THEN  'True'  ELSE 'False'  END AS column3
FROM
(
SELECT col1,
      column2,
      CAST(CASE WHEN PATINDEX('%[A-Z a-z]%',RIGHT(col1,4)) = 0 THEN RIGHT(col1,4)  ELSE ' '  END AS INT) newcol,
      CASE WHEN PATINDEX('%[A-Z a-z]%',RIGHT(col1,4)) = 0 THEN 'True'  ELSE 'False'  END  getcol

FROM CTE
) Dt 

Result

col1        column2 column3
----------------------------
585550529   red     True
589960MNO   blue    False
849828415   green   False

Beginning SQL Server 2000 Programming, This was mentioned in Chapter 1 when SQL Server was installed, and is covered the Server behavior section, refers to some quite advanced subject areas, so for the entry into SQL Server, that has only the last two digits of the year number entered. SQL Server needs to know how this will be represented, in other words,  The function returns 1 for numbers that include symbols like +, -, $, etc. As per my use case, I need to validate decimal and integer values. SQL Server User Defined Functions for Integer and Decimal Validation. Next, I will create new user defined functions to validate integer and decimal values as per my use case.

LEFT (Transact-SQL), For more information, see Collation and Unicode Support. Examples. A. Using LEFT with a column. The following example returns the five  SQL CHECK Constraint. The CHECK constraint is used to limit the value range that can be placed in a column. If you define a CHECK constraint on a single column it allows only certain values for this column. If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.

Pro T-SQL 2019: Toward Speed, Scalability, and Standardization for , Toward Speed, Scalability, and Standardization for SQL Server Developers However, if it is only necessary to know on what day an action occurred, then the In this case YYYY represents the four-digit year with the range of 0001 to 9999. The DATE values that can be stored range from 0001-01-01 to 9999-12-31. Summary: in this tutorial, you will learn how to use the SQL Server IN operator to check whether a value matches any value in a list.. SQL Server IN operator overview. The IN operator is a logical operator that allows you to test whether a specified value matches any value in a list.

How to Filter Query Results using WHERE, A where clause is a condition that must be met in order for a row to be returned in a query. Sometime you want to know when a value falls within a range, such as quantities greater Thus to find all persons with a last name less than or equal to Nixon you would write Unfortunately that isn't the case with SQL Server. Converting from int, smallint, tinyint, float, real, money, or smallmoney to either decimal or numeric can cause overflow. By default, SQL Server uses rounding when converting a number to a decimal or numeric value with a lower precision and scale. Conversely, if the SET ARITHABORT option is ON,

Comments
  • @MartyB Yes it does