2

I'm trying to turn my IP address into set of 3-digit numbers, left padded with 0. I can easily do that like so:

DECLARE @merged VARCHAR(15);
SELECT @marged = COALESCE(@merged + '.', '') + RIGHT('000' + [value], 3)
FROM string_split(ip_address, '.');

But now I want to do something like that within a broader select statement.

SELECT Name, @merged AS IP_Address, Mac_Address
FROM some_table

I'm not sure how to get the @merged part there though when I'm querying against a full table.

Gargoyle
  • 9,590
  • 16
  • 80
  • 145

2 Answers2

0

To get the comma values:

Return Comma separated values SQL

To get them grouped:

Comma Separated list of rows of a column with group by on other columns

http://www.peteonsoftware.com/index.php/2009/12/11/creating-a-comma-separated-list-from-a-sql-grouping/

SAS
  • 3,943
  • 2
  • 27
  • 48
  • That's still got that same problem of how to do the `select @merged =` part of the query though from what I can tell. – Gargoyle May 31 '18 at 05:53
0

You have a specific problem with IP addresses, so you can use parse_name():

SELECT Name, 
       (left('000' + parse_name(Mac_Address, 4), 3) + '.' +
        left('000' + parse_name(Mac_Address, 3), 3) + '.' +
        left('000' + parse_name(Mac_Address, 2), 3) + '.' +
        left('000' + parse_name(Mac_Address, 1), 3) 
       ) as ip_address,
      Mac_Address
FROM some_table;

This is going to be simpler and better performing that other solutions use string aggregation.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • @Gargoyle . . . `parsename()` has existed as long as I've used SQL Server. I think it was in SQL Server 2000. Here is an old reference: http://www.sqlteam.com/article/using-the-parsename-function-to-split-delimited-data. – Gordon Linoff Jun 01 '18 at 01:40