删除 SQL 中 IP 地址的前导零 [英] Remove Leading Zeros On Ip Address in SQL

问题描述

我有一个名为 IP 的列，其中包含诸如 10.001.99.108

I have a column called IP with data such as 10.001.99.108

我想运行一个脚本将其更改为 10.1.99.108

I want to run a script to change it to look like 10.1.99.108

我以前用过这个:

update TABLE set IP =  substring(IP, patindex('%[^0]%',IP), 10) 

但这会删除乞讨处的前导零.

but that removes leading zeros at the begging.

我不知道如何改变它来做第二段.

Im not sure how I could change it to do the second segment.

推荐答案

您可以使用 parsename() 和删除前导零的方法来完成此操作.以下通过转换为整数然后返回字符串来删除前导零:

You can do this with parsename() and a method to remove the leading zeros. The following removes the leading zeros by casting to an integer and then back to string:

select (cast(cast(parsename(ip, 4) as int) as varchar(255)) +
        cast(cast(parsename(ip, 3) as int) as varchar(255)) +
        cast(cast(parsename(ip, 2) as int) as varchar(255)) +
        cast(cast(parsename(ip, 1) as int) as varchar(255))
       )

这篇关于删除 SQL 中 IP 地址的前导零的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！