Sql-server – Parse a column with a “range” in a WHERE in SQL Server

sql serversql-server-2012

I'm querying a table that has several alphanumeric key columns…

KEY1  KEY2  KEY3 ... SOMEDATA  SOMEOTHERDATA...
s001
s002
s003  s004  s005
s006

The keys are the PK from another table we need to join on, which is annoying but not difficult. But then they decided to do this…

s010-s105

Which means s010, s011…s015. Is there any way to do a query that breaks this out so I can find a given key, say s013, in these columns?

Best Answer

T-SQL - 246 Bytes

Quick and dirty:

create table #DataInRanges (KeyId varchar(10), FirstName varchar(10), LastName varchar(10));

insert into #DataInRanges (KeyId, FirstName, LastName) values
('s010-s015', 'Nelson', 'Casanova'),
('s009-s010', 'Karl', 'Johnson'),
('s090-s110', 'Michael', 'Buxton'),
('s079-s080', 'Terrence', 'Dickson'),
('s010-s030', 'Mario', 'Carrizo'),
('s025-s085', 'Victor', 'Lee'),
('s019-s040', 'Jim', 'Buckney'),
('s030-s110', 'Solomon', 'Bennett')

declare @ANumber INT = 20;

select * 
from
(
    select
        cast(replace(parsename(replace(KeyId, '-','.'),2), 's', '') as int) as Key1, 
        cast(replace(parsename(replace(KeyId, '-','.'),1), 's', '') as int) as Key2
    from #DataInRanges
) t
where (@ANumber) between t.Key1 and t.Key2

-----------
|key1|key2|
-----------
| 10 | 30 |
| 19 | 40 |
-----------

With the caveat that parsename wont dig beyond 4 levels

Related Solutions

Sql-server – SQL Server: join two tables with “valid-to” dates

DECLARE @t1 TABLE (T1_key int, T1_Data char(1), T1_ValidUntil datetime)
DECLARE @t2 TABLE (T2_key int, T2_Data char(1), T2_ValidUntil datetime)

INSERT @t1 VALUES (1, 'A', '2000-01-01')
INSERT @t1 VALUES (1, 'B', '2000-06-30')
INSERT @t1 VALUES (2, 'C', '2005-05-31')
INSERT @t1 VALUES (3, 'D', '2004-12-31')
INSERT @t1 VALUES (3, 'E', '2007-04-30')
INSERT @t1 VALUES (3, 'F', '2008-01-31')

INSERT @t2 VALUES (1, 'R', '2002-03-31')
INSERT @t2 VALUES (2, 'S', '2001-06-30')
INSERT @t2 VALUES (2, 'T', '2003-02-28')
INSERT @t2 VALUES (2, 'U', '2005-05-31')
INSERT @t2 VALUES (3, 'V', '2006-09-30')
INSERT @t2 VALUES (3, 'W', '2007-06-30')

SELECT
   T1.*, T2x.*
FROM
   @t1 T1
   CROSS APPLY
   (SELECT TOP 1*
   FROM @t2 
   WHERE T1_key = T2_key AND T2_ValidUntil >= T1_ValidUntil
   ORDER BY T2_ValidUntil
   ) T2x
UNION
SELECT
   T1x.*, T2.*
FROM
   @t2 T2
   CROSS APPLY
   (SELECT TOP 1*
   FROM @t1
   WHERE T1_key = T2_key AND T1_ValidUntil >= T2_ValidUntil
   ORDER BY T1_ValidUntil
   ) T1x

Sql-server – Get NULL statistics for a column in MS SQL Server

Nothing so easy. You would need to use DBCC SHOW_STATISTICS and look at the histogram, taking into account that the statistics might be filtered or multi column.

Probably easier to just let SQL Server do it and generate an estimated execution plan for the statement select * from foo where bar is null SQL Server will then use appropriate statistics if they exist or create them if they don't (assuming that the auto create statistics option is enabled).

You can then look at the estimated number of rows going into the SELECT iterator (This shows up as StatementEstRows in the XML for purposes of parsing programatically)

Example code

/*Create temporary stored procedure with query of interest*/
CREATE PROC dbo.#plan_test
AS
SELECT *
FROM master..spt_values
WHERE low IS NULL

GO

/*Generate an execution plan*/
SET FMTONLY ON
EXEC dbo.#plan_test
SET FMTONLY OFF;

/*Retrieve the plan and parse the estimated rows*/
WITH  XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as sql) 
SELECT  query_plan.value('(//sql:StmtSimple[1]/@StatementEstRows)[1]', 'float') AS EstimatedNumberOfRows
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
CROSS APPLY sys.dm_exec_query_plan(plan_handle) 
WHERE sys.dm_exec_sql_text.objectid=OBJECT_ID('tempdb.dbo.#plan_test') 
     AND sys.dm_exec_sql_text.dbid=2

DROP PROC dbo.#plan_test