I have the following given string formats.

DECLARE @IPData_1 nvarchar(max) = '{"ODBC":"Connection-StringIPX-IP=10.60.96.230&X-Local}'
DECLARE @IPData_2 nvarchar(max) = '{"ODBC":"Connection-StringIPX-IP:1.60.0.230X-Local=1}'
DECLARE @IPData_3 nvarchar(max) = '{"ODBC":"Connection-StringIPX-For=233.60.255.120TransactionID:2}'
DECLARE @IPData_4 nvarchar(max) = '{"ODBC":"Connection-StringIPX-For:1.1.1.90ID=1}'

I want to get only IP Address that is:

10.60.96.230
1.60.0.230
233.60.255.120
1.1.1.90

My try:

SELECT
    SUBSTRING(@IPData_1,CHARINDEX('IPX-IP=',@IPData_1) 7,CHARINDEX('.',@IPData_1)),
    SUBSTRING(@IPData_1,CHARINDEX('IPX-IP:',@IPData_1) 7,CHARINDEX('.',@IPData_1)),
    SUBSTRING(@IPData_1,CHARINDEX('IPX-For=',@IPData_1) 7,CHARINDEX('.',@IPData_1)),
    SUBSTRING(@IPData_1,CHARINDEX('IPX-For:',@IPData_1) 7,CHARINDEX('.',@IPData_1))

CodePudding user response：

SQL Server is not the optimal place to perform string manipulation.

Having said that, sometimes we don't have the option to do so elsewhere.

DECLARE @Table TABLE (IPData NVARCHAR(100));
INSERT INTO @Table (IPData) VALUES
('{"ODBC":"Connection-StringIPX-IP=10.60.96.230&X-Local}'),
('{"ODBC":"Connection-StringIPX-IP:1.60.0.230X-Local=1}'),
('{"ODBC":"Connection-StringIPX-For=233.60.255.120TransactionID:2}'),
('{"ODBC":"Connection-StringIPX-For:1.1.1.90ID=1}');      

SELECT IPData, SUBSTRING(IPData, PATINDEX('%[IP|FOR][:|=]%', IPData) 2, PATINDEX('%[0-9][&|X|T|ID]%', IPData)-(PATINDEX('%[IP|FOR][:|=]%', IPData))-1) AS IPData
  FROM @Table;

Here we are using the PATINDEX function to find the locations of patterns, and using SUBSTRING to create a substring using those pattern locations.