question

vjayapathy avatar image
vjayapathy asked

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.

sql-server-2008sql-server-2008-r2sqlserver
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Can you post a script that sets up the data you want us to go against? The text above isn't clear.

0 Likes 0 ·

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?

0 Likes 0 ·
Show more comments
ThomasRushton avatar image
ThomasRushton answered

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,
                                  ''
                              );
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jon Crawford avatar image
Jon Crawford answered

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].%';

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

While that would work for this particular case, it's not a general solution...

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.