split a comma separated string into columns
-
Hi
Our front end saves all IP addresses used by a customer as a comma separated string, we need to analyse these to check for blocked IPs which are all stored in another table.
A LIKE statement comparing each string with the 100 or so excluded IPs will be very expensive so I'm thinking it would be less so to split out the comma separated values into tables.
The problem we have is that we never know how many IPs could be stored against a customer, so I'm guessing a function would be the way forward but this is the point I get stuck.
This is what I've got so far, I can remove the 1st IP address into a new column and produce the new list ready for the next removal, also as part of this we would need to create new columns on the fly depending on how many IPs are in the column.
This needs to be repeated for each row
SELECT IP_List
, LEFT(IP_List, CHARINDEX(',', IP_List) - 1) AS IP_1
, REPLACE(IP_List, LEFT(IP_List, CHARINDEX(',', IP_List) +0), '') AS NewIPList1
FROM IpExclusionTest
Results:
IP_List
109.224.216.4,146.90.13.69,146.90.85.79,46.208.122.50,80.189.100.119
IP_1
109.224.216.4
NewIPList1
146.90.13.69,146.90.85.79,46.208.122.50,80.189.100.119
-
Have a look here
http://www.sqlservercentral.com/articles/Tally+Table/72993/
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Mark Cowne (4/24/2015)
Have a look hereGreat, thanks, as much as this answer was over-complicated from what we needed, the function he mentions does exactly what we need
-
cerian (4/24/2015)
Mark Cowne (4/24/2015)
Have a look hereGreat, thanks, as much as this answer was over-complicated from what we needed, the function he mentions does exactly what we need
I would disagree that it is over-complicated. Splitting strings is difficult (as you discovered). If he didn't explain how the code worked and tested the performance of it he would have done you a great disservice. It doesn't help if you have code that works but you have no idea how or why it works. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Sean Lange (4/24/2015)
cerian (4/24/2015)
Mark Cowne (4/24/2015)
Have a look hereGreat, thanks, as much as this answer was over-complicated from what we needed, the function he mentions does exactly what we need
I would disagree that it is over-complicated. Splitting strings is difficult (as you discovered). If he didn't explain how the code worked and tested the performance of it he would have done you a great disservice. It doesn't help if you have code that works but you have no idea how or why it works. 😉
I have to agree with Sean's disagreement. If you don't understand how the code works, you won't be able to apply it in other areas.
Like Sean pointed out, splitting strings can be complicated, both in terms of functionality and performance. I (along with others) have been pretty far down the road of playing with DelimitedSplit8K. It's designed to one thing and do it very well. Using the lessons learned by that single function can lead to different approaches to other problems. Understanding how it works is key to improving other things.
-
It's complicated in T-SQL, but not overly so. However, I agree with Sean and Ed. If you think this is overcomplicated, I think you need to work on better understanding it. Don't implement code that you don't understand. If it breaks, what will you do?
-
To be honest, the front end is storing the data incorrectly. The data should be sorted with just one IP address per row and, once the table is properly indexed, the lookups would be lightning fast and with no splitting needed. It would also make de-duping the data MUCH easier.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (4/24/2015)
To be honest, the front end is storing the data incorrectly. The data should be sorted with just one IP address per row and, once the table is properly indexed, the lookups would be lightning fast and with no splitting needed. It would also make de-duping the data MUCH easier.Further on Jeff's comment, storing the IP as integer and adding A,B,C class masks makes it much more efficient;-)
😎
Quick sample to play with
USE tempdb;GO
SET NOCOUNT ON;
GO
IF OBJECT_ID(N'dbo.ITVF_IP_NUMERICAL') IS NOT NULL DROP FUNCTION dbo.ITVF_IP_NUMERICAL;
EXEC (N'CREATE FUNCTION dbo.ITVF_IP_NUMERICAL
(
@IPADDR VARCHAR(15)
)
RETURNS TABLE
RETURN
/*
ITVF_IP_NUMERICAL
Converting an character representation of an IP address into
an integer and masking A,B,C classes
2015-04-26 Eirikur Eiriksson
*/
WITH IP_ADDRESS_DELIM AS
(
SELECT
CHARINDEX(CHAR(46),@IPADDR,1) AS POS_01
,CHARINDEX(CHAR(46),@IPADDR,CHARINDEX(CHAR(46),@IPADDR,1) + 1) AS POS_02
,CHARINDEX(CHAR(46),@IPADDR,CHARINDEX(CHAR(46),@IPADDR,CHARINDEX(CHAR(46),@IPADDR,1) + 1) + 1) AS POS_03
,LEN(@IPADDR) AS POS_04
)
,NUMERICAL_IP AS
(
SELECT
CONVERT(INT,
CONVERT(BINARY(1),CONVERT(TINYINT,SUBSTRING(@IPADDR,IAD.POS_03 + 1,(IAD.POS_04 -IAD.POS_03) ),0),1)
+ CONVERT(BINARY(1),CONVERT(TINYINT,SUBSTRING(@IPADDR,IAD.POS_02 + 1,(IAD.POS_03 -IAD.POS_02) -1),0),1)
+ CONVERT(BINARY(1),CONVERT(TINYINT,SUBSTRING(@IPADDR,IAD.POS_01 + 1,(IAD.POS_02 -IAD.POS_01) -1),0),1)
+ CONVERT(BINARY(1),CONVERT(TINYINT,SUBSTRING(@IPADDR,1,IAD.POS_01 -1) ,0),1)
) AS IP_INT
FROM IP_ADDRESS_DELIM IAD
)
SELECT
NI.IP_INT & 0xFF AS IP_AX
,NI.IP_INT & 0xFFFF AS IP_BX
,NI.IP_INT & 0xFFFFFF AS IP_CX
,NI.IP_INT AS IP_DX
FROM NUMERICAL_IP NI;
');
DECLARE @IP_LIST TABLE (IP_ID INT IDENTITY(1,1) NOT NULL,IPADDR VARCHAR(15) NOT NULL);
INSERT INTO @IP_LIST(IPADDR)
VALUES
('192.168.25.2')
,('192.168.25.22')
,('192.168.25.222')
,('192.168.25.2')
,('192.168.26.2')
,('192.168.27.2')
,('192.168.25.2')
,('192.169.25.2')
,('192.167.25.2')
,('192.168.25.2')
,('193.168.25.2')
,('194.168.25.2')
;
SELECT
IL.IP_ID
,IL.IPADDR
,X.IP_AX
,X.IP_BX
,X.IP_CX
,X.IP_DX
FROM @IP_LIST IL
CROSS APPLY dbo.ITVF_IP_NUMERICAL(IL.IPADDR) AS X;
Sample output
IP_ID IPADDR IP_AX IP_BX IP_CX IP_DX----------- --------------- ----------- ----------- ----------- -----------
1 192.168.25.2 192 43200 1681600 35236032
2 192.168.25.22 192 43200 1681600 370780352
3 192.168.25.222 192 43200 1681600 -568743744
4 192.168.25.2 192 43200 1681600 35236032
5 192.168.26.2 192 43200 1747136 35301568
6 192.168.27.2 192 43200 1812672 35367104
7 192.168.25.2 192 43200 1681600 35236032
8 192.169.25.2 192 43456 1681856 35236288
9 192.167.25.2 192 42944 1681344 35235776
10 192.168.25.2 192 43200 1681600 35236032
11 193.168.25.2 193 43201 1681601 35236033
12 194.168.25.2 194 43202 1681602 35236034
-
Thanks all for your input. I wasn't criticising the amount of information given, that was great and did what it needed to do - helped me get my resolution.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply