searching for pattern in t-sql

Question

I have a db columns with values like 100.23.24.1, 100.23.24.2, 100.23.24.3 etc. I have to find the last number after the last dot. In this case I want 3. Then I want to increment the last number and generate new entry as 100.23.24.4. How can I do this. Can someone please help?

I cannot look for the 100.23.24. pattern because this may change in another database column like: 100.23.25.1, 100.23.25.2. In this case I should get the last number as 2

Answer 1

• Use REVERSE an CHARINDEX to find the last dot
• Feed into SUBSTRING and REVERSE this bit again to get last number
• Preserve remainder of reserved string
• CAST to integer, add one, CAST to string
• Append remainder of reserved string and newly incremented number
• REVERSE

or store it properly

• You can also use nested CHARINDEX+1 to find the 3rd dot without REVERSE

or store it properly

• Do this in the client or CLR code which has a handy string.split feature

or stor... you get the idea

Answer 2

best way:

create 4 new columns of tiny or small int values, and then populate them from your string column. At that point drop the string column. You can now easily do an:

UPDATE YourTable Set col4=col4+1 WHERE col4=3

You could add a computed column to concatenate the 4 values back into a string if you wish, for support of old code.

--set up existing table
create table YourTable (YourColumn varchar(20))
INSERT YourTable VALUES ('100.23.24.1')
INSERT YourTable VALUES ('100.23.24.2')
INSERT YourTable VALUES ('100.23.24.3')
INSERT YourTable VALUES ('100.23.24.9')

--add in new columns
ALTER TABLE YourTable ADD  Col1 int null
ALTER TABLE YourTable ADD  Col2 int null
ALTER TABLE YourTable ADD  Col3 int null
ALTER TABLE YourTable ADD  Col4 int null

--populate the new columns, split apart the string
UPDATE YourTable
    SET Col1=PARSENAME(YourColumn,4)
       ,Col2=PARSENAME(YourColumn,3)
       ,Col3=PARSENAME(YourColumn,2)
       ,Col4=PARSENAME(YourColumn,1)

--remove the string column
ALTER TABLE YourTable drop column YourColumn

--add back the string column as a computed column
ALTER TABLE dbo.YourTable ADD YourColumn  AS CONVERT(varchar(10),Col1)+'.'+CONVERT(varchar(10),Col2)+'.'+CONVERT(varchar(10),Col3)+'.'+CONVERT(varchar(10),Col4)

--show the table's contents
select * from YourTable

OUTPUT:

Col1        Col2        Col3        Col4        YourColumn
----------- ----------- ----------- ----------- -------------
100         23          24          1           100.23.24.1
100         23          24          2           100.23.24.2
100         23          24          3           100.23.24.3
100         23          24          9           100.23.24.9

(4 row(s) affected)

short of that here is a quick brute force table scan way:

UPDATE YourTable
    SET YourColumn=LEFT(YourColumn,LEN(YourColumn)-2)+'.4'
    WHERE LEFT(REVERSE(YourColumn),2)='3.'

sample code:

declare @YourTable table (YourColumn varchar(20))
INSERT @YourTable VALUES ('100.23.24.1')
INSERT @YourTable VALUES ('100.23.24.2')
INSERT @YourTable VALUES ('100.23.24.3')
INSERT @YourTable VALUES ('100.23.24.9')


UPDATE @YourTable
    SET YourColumn=LEFT(YourColumn,LEN(YourColumn)-2)+'.4'
    WHERE LEFT(REVERSE(YourColumn),2)='3.'

select * from @YourTable

OUTPUT:

YourColumn
--------------------
100.23.24.1
100.23.24.2
100.23.24.4
100.23.24.9

Answer 3

For SQL Server, the query below shows you how to extract the left and right portions to build a new value:

DECLARE @ip varchar(20)
SET @ip = '100.23.24.1'

SELECT left(@ip, len(@ip) - charindex('.', reverse(@ip))) + '.' + 
  cast(right(@ip, charindex('.', reverse(@ip)) - 1) + 1 as varchar(3))

In this case, it will return 100.23.24.2.

Answer 4

WITH ips AS
(
SELECT '100.23.24.123' AS ip UNION ALL
SELECT '100.23.24.1' AS ip
)


SELECT LEFT(ip, PATINDEX('%' + PARSENAME(ip,1),ip) -1) + 
              CAST(CAST(PARSENAME(ip,1) AS INT)+1 AS VARCHAR)
FROM ips