在论坛中出现的比较难的sql问题:16(取一个字段中的数字)

本文介绍了一种使用SQL从包含多种字符的VARCHAR字段中仅提取数字的方法。通过使用PATINDEX和LEFT函数组合,可以有效地过滤掉非数字字符。
最近,在论坛中,遇到了不少比较难的sql问题,虽然自己都能解决,但发现过几天后,就记不起来了,也忘记解决的方法了。

所以,觉得有必要记录下来,这样以后再次碰到这类问题,也能从中获取解答的思路。



问题:取一个字段中的数字
http://bbs.youkuaiyun.com/topics/390647599


有一个字段是VARCHAR类型,其中包含了很多字符。但这些字符中有些是汉字,有些是数字,有些是一些特殊字符比如全角的括号'()'也有半角的括号'()'也有'/','-' 等,我想的是只留下数字,比如:(XX)123455那么就只保留123455
比如:中123455只保留123455
是否可以用ASCII码来进行比较,有没有好的算法,或者其他方案。


我的写法:

;with t
as
(
select '(xx)1234' v union all
select 'xx1234xyz' v union all
select '1234'
)

select case when patindex('%[^0-9]%',v) = 0
                 then v
            else LEFT(v,patindex('%[^0-9]%',v)-1)
       end as v
from 
(
select stuff(v,1,patindex('%[0-9]%',v)-1,'') as v              
from t
)t


<think>好的,我现在需要帮助用户解决SQL Server中从字段左侧截到某个指定字符的问题。用户提到他们想要截字段从左开始到某个字符结束,例如像"abc_123"截到第一个下划线为止,得到"abc"。我需要回忆一下SQL Server中的字符串函数,找到合适的方法。 首先,用户提供的引用内容里有提到SUBSTRING、CHARINDEX、PATINDEX等函数。引用[1]中使用了SUBSTRING和CHARINDEX来截两个字符之间的部分,这可能可以调整一下。比如,如果用户想从左边开始截到某个字符,比如下划线,那么需要找到该字符的位置,然后用SUBSTRING从位置1开始,截到该位置减一的位置。 例如,假设字段是'abc_123',要找第一个下划线的位置,用CHARINDEX('_', 字段)返回的位置是4,那么截长度就是4-1=3,所以SUBSTRING(字段,1,3)就是'abc'。这种方法可行。 但需要考虑如果字段中没有该字符的情况,这时候CHARINDEX会返回0,直接减一的话长度会是负数,导致SUBSTRING出错。所以需要处理这种情况,可能用CASE WHEN判断,当CHARINDEX的结果大于0时才截,否则返回原字符串或者空值。 另外,引用[3]中使用了PATINDEX来查找第一个符合模式的字符的位置,比如非字母数字之前的字符。这里用户可能需要的是找到第一个出现的特定字符,所以CHARINDEX更直接。过如果用户的需求是更复杂的模式,比如第一个非字母数字的位置,可能需要用PATINDEX,但用户的问题里明确提到的是指定字符,比如下划线,所以CHARINDEX更合适。 所以,基本的步骤应该是: 1. 使用CHARINDEX查找指定字符在字段中的位置。 2. 如果找到(位置>0),使用SUBSTRING从1开始,截长度是位置-1。 3. 如果没找到,可能需要返回整个字段或者空字符串,决于用户的需求。 例如,假设字段为@str,指定字符为@char,那么SQL语句可以是: SELECT CASE WHEN CHARINDEX(@char, @str) > 0 THEN SUBSTRING(@str, 1, CHARINDEX(@char, @str) -1) ELSE @str END 这样就能处理存在和存在的情况。用户可能需要根据实际情况调整ELSE部分,比如返回NULL或者原字符串。 此外,用户提供的引用[1]中的自定义函数可能也可以参考,但用户可能更倾向于使用内置函数而是创建函数。过如果用户需要频繁使用,封装成函数可能更方便。 还需要注意,如果字段中有多个指定字符的情况,例如'abc_def_ghi',用户可能只想要到第一个出现的字符为止,这时候CHARINDEX默认返回第一个出现的位置,所以没问题。但如果用户想要到最后一个出现的字符,那就需要用同的方法,但用户的问题明确是到某个字符为止,可能指的是第一个出现的。 测试一下示例数据,比如: DECLARE @str VARCHAR(20) = 'abc_123'; SELECT SUBSTRING(@str, 1, CHARINDEX('_', @str) -1) AS Result; 这会返回'abc'。如果@str是'abcdef'(没有下划线),那么CHARINDEX返回0,减一后是-1,SUBSTRING会报错,所以必须用CASE WHEN来处理。 综上,正确的做法应该是结合CHARINDEX和SUBSTRING,并用CASE处理存在的情况。用户可能需要具体的代码示例,所以给出示例代码,并说明如何处理存在的情况。</think>在SQL Server中实现从左截到指定字符的方法,可以通过组合使用`CHARINDEX`和`SUBSTRING`函数实现。以下是具体实现步骤和示例: --- ### **方法实现** 1. **定位字符位置**:使用`CHARINDEX('目标字符', 字段)`获指定字符首次出现的位置。 2. **截子字符串**:通过`SUBSTRING(字段名, 起始位置, 截长度)`从左侧开始截到目标字符前一位。 3. **处理未找到的情况**:用`CASE WHEN`判断字符是否存在,避免因位置为0导致错误。 #### **语法示例** ```sql SELECT CASE WHEN CHARINDEX('指定字符', 字段) > 0 THEN SUBSTRING(字段名, 1, CHARINDEX('指定字符', 字段) - 1) ELSE 字段名 -- 或返回空字符串、NULL END AS 结果列名 FROM 表名; ``` --- ### **实际案例** 假设表`Products`中字段`ProductCode`存储数据为`A001-BLUE`,需截`-`前的部分: ```sql SELECT CASE WHEN CHARINDEX('-', ProductCode) > 0 THEN SUBSTRING(ProductCode, 1, CHARINDEX('-', ProductCode) - 1) ELSE ProductCode END AS ShortCode FROM Products; ``` **结果**:`A001` --- ### **扩展场景** 1. **处理多字符分隔符** 若需截到多字符(如`##`),可用`PATINDEX`匹配模式: ```sql SELECT CASE WHEN PATINDEX('%##%', 字段) > 0 THEN SUBSTRING(字段名, 1, PATINDEX('%##%', 字段) - 1) ELSE 字段名 END AS Result FROM 表名; ``` 2. **自定义函数封装** 参考引用[1]创建可复用函数: ```sql CREATE FUNCTION dbo.LeftUntilChar (@str VARCHAR(MAX), @char CHAR(1)) RETURNS VARCHAR(MAX) AS BEGIN RETURN ( CASE WHEN CHARINDEX(@char, @str) > 0 THEN SUBSTRING(@str, 1, CHARINDEX(@char, @str) - 1) ELSE @str END ) END ``` **调用示例**: ```sql SELECT dbo.LeftUntilChar('Hello_World', '_') -- 返回'Hello' ``` --- ### **注意事项** - 若字段中存在多个目标字符(如`a_b_c`),仅截到第一个出现的位置[^2]。 - 目标字符区分大小写,若需模糊匹配,可先用`LOWER()`或`UPPER()`统一格式[^3]。 ---
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值