How to select multiple dot values in column in sql server?
I need to select the value between 5.0.1.0 to 5.0.12.0:
Create table Test (id int, value varchar(20)); Insert into Test values(1,'5.0.11.0'),(2, '5.0.100.0'),(3,'5.0.14.0'),(4, '5.0.5.0'),(5, '5.1.1.0'),(6,'5.0.12.0'),(7,'5.0.10.2)');
Id Values 1 5.0.11.0 2 5.0.100.0 3 5.0.14.0 4 5.0.5.0 5 5.1.1.0 6 5.0.12.0 7 5.0.10.2
My output I need:
Id Values 1 5.0.11.0 4 5.0.5.0 6 5.0.12.0 7 5.0.10.2
Thanks.
Can you post a script that sets up the data you want us to go against? The text above isn't clear.
Sorry, more questions.
The output you give - some of those values don't appear in your data? And how does 5.0.12.07 fit in the range 5.0.1.0 - 5.0.12.0?
Now you've provided more easily consumable data, I can see where I was going wrong here. :-) Thanks!
or use LIKE:
SELECT * FROM Test
WHERE
(
value LIKE '%5.0.[0-9].%'
OR value LIKE '%5.0.1[012].%'
)
AND value NOT LIKE '%5.0.[0-9][0-9][0-9].%';
While that would work for this particular case, it's not a general solution...
Ugh. The problem here is the mixed / variable lengths of the various data subfields.
The approach, therefore, must be to normalise / regularise those, and then compare against a similarly-regularised pair of parameters.
The way I've done that here is by using a splitter function (I'm using Jeff Moden's DelimitedSplit8K function, as you're on SQL 2008 R2) to break down the value field into its component parts, then left padding each one with zeroes to a total of eight characters, and do the same to the parameters, and then stick them all back together and see what's going on. It's a bit messy.
Here goes:
--I'm using a table variable rather than your test table. You can figure that bit out.
DECLARE @Test TABLE (id INT, value VARCHAR(20));
INSERT INTO
@Test
VALUES
(1, '5.0.11.0'),
(2, '5.0.100.0'),
(3, '5.0.14.0'),
(4, '5.0.5.0'),
(5, '5.1.1.0'),
(6, '5.0.12.0'),
(7, '5.0.10.2');
SELECT * FROM @Test; -- just to check
--WHERE value >= '5.0.1.0' AND value <= '5.0.12.0'
--expected output:
--Id Values
--1 5.0.11.0
--4 5.0.5.0
--6 5.0.12.0
--7 5.0.10.2
WITH
stuffedvalues
AS
(
SELECT id,
value,
STUFF(
(
SELECT '.' + RIGHT('00000000' + Item, 8) AS padval
FROM
@Test t1
CROSS APPLY dbo.DelimitedSplit8K(t1.value, '.')
WHERE t.id = t1.id
ORDER BY
id,
ItemNumber
FOR XML PATH(''), TYPE
).value('.', 'varchar(100)'),
1,
1,
''
) AS stuffedvalue
FROM @Test t
GROUP BY
id,
value
)
SELECT id, value, stuffedvalue -- to show what they look like
FROM stuffedvalues
WHERE
stuffedvalue BETWEEN STUFF(
(
SELECT '.' + RIGHT('00000000' + Item, 8) AS padval
FROM dbo.DelimitedSplit8K('5.0.1.0', '.')
FOR XML PATH(''), TYPE
).value('.', 'varchar(100)'),
1,
1,
''
)
AND STUFF(
(
SELECT '.' + RIGHT('00000000' + Item, 8) AS padval
FROM dbo.DelimitedSplit8K('5.0.12.0', '.')
FOR XML PATH(''), TYPE
).value('.', 'varchar(100)'),
1,
1,
''
);
