问题描述
您好，
我正在尝试使用8位字符串更新表格中的列。
从另一列获取8位数字这是一个评论
字段。
例如
评论字段：
1）RD-V0216970 RQST NON SMKING KING未知＃：22338921
2）收到口头授权授权＃040345
3）国际广场，新加坡＃96722540
我正在使用的代码是
UPDATE U SET U.NUM =
CASE
当U.BOOKED_COMMENTS_TXT喜欢
（''％[0123456789] [0123456789] [0123456789] [0123456789] [0123456789] [0123456789]％''）
那么
SUBSTRING（U.BOOKED_COMMENTS_TXT，
PATINDEX（''％[0123456789] [0123456789] [0123456789] [0123456789] [0123456789] [0123456789]％'' ，
U.BOOKED_COMMENTS_TXT），8）
ELSE NULL
END
来自UNKNOWN1_RESERVATIONS U
这里我的结果集是什么样的
1）V0216970
2）040345
3）96722540
但这就是我希望我的结果集看起来像
1）22338921
2）null
3）96722540
我需要的是一种将搜索条件限制为8位数b / b
数字的方法。任何建议都会有所帮助。
提前致谢
Hello,
I am trying to update a column in a table with an 8 digit string.
The 8 digit number is obtained from another column which is a comments
field.
e.g.
Comments Field :
1) RD-V0216970 RQST NON SMKING KING Unknown # : 22338921
2) Received verbal authorization Authorization # 040345
3) international plaza, singapore # 96722540
The code that I am using is
UPDATE U SET U.NUM =
CASE
WHEN U.BOOKED_COMMENTS_TXT LIKE
(''%[0123456789][0123456789][0123456789][0123456789][0123456789][0123456789]%'')
THEN
SUBSTRING(U.BOOKED_COMMENTS_TXT,
PATINDEX(''%[0123456789][0123456789][0123456789][0123456789][0123456789][0123456789]%'',
U.BOOKED_COMMENTS_TXT), 8)
ELSE NULL
END
FROM UNKNOWN1_RESERVATIONS U
Here''s what my result set looks like
1)V0216970
2)040345
3)96722540
But this is how I want my result set to look like
1)22338921
2)null
3)96722540
What I need is a way to restrict the search criteria to exactly 8
numeric digits. Any suggestions will be helpful.
Thanks in advance
推荐答案
从示例中可以看出，您要查找的字符串是
总是在评论的末尾领域。在这种情况下，这可以做到：
UPDATE tbl
SET col = CASE WHEN reverse（str）
LIKE'' [0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [^ 0-9] ''
那么对（str，8）
ELSE NULL
结束
-
Erland Sommarskog，SQL Server MVP，
适用于SQL Server SP3的联机丛书
你好SQL_developer，
您查询失败的原因是您搜索了6个数字
个字符而不是8个。更改它解决了它。我还用更加可移植的ANSI标准
UPDATE语法替换了
专有的UPDATE FROM语法 - 在这种情况下，没有理由使用UPDATE
FROM！
CREATE TABLE UNKNOWN1_RESERVATIONS
（ID int NOT NOT PRIMARY KEY，
NUM char（ 8），
BOOKED_COMMENTS_TXT varchar（200））
go
INSERT INTO UNKNOWN1_RESERVATIONS（ID，BOOKED_COMMENTS_TXT）
SELECT 1，''RD-V0216970 RQST NON SMKING KING未知＃：22338921''
UNION ALL
SELECT 2，''收到口头授权授权＃040345''
UNION ALL
SELECT 3，''国际广场，新加坡＃96722540不在中间''
go
UPDATE UNKNOWN1_RESERVATIONS
SET NUM = CASE
当BOOKED_COMMENTS_TXT类似
''％[0-9] [0-9] [0- 9] [0-9] [0-9] [0-9] [0-9] [0-9]％''
那么SUBSTRING（BOOKED_COMMENTS_TXT，
PATINDEX（''％[0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0 -9]％''，
BOOKED_COMMENTS_TXT），8）
ELSE NULL
END
go
SELECT * FROM UNKNOWN1_RESERVATIONS
go
DROP TABLE UNKNOWN1_RESERVATIONS
go
Best，Hugo
-
（删除_NO_和_SPAM_以获取我的电子邮件地址）
Hi SQL_developer,
The reason your query failed is that you searched for 6 numeric
characters instead of 8. Changing that solves it. I also replace the
proprietary UPDATE FROM syntax with the much more portable ANSI-standard
UPDATE syntax - in this case, there is no reason at all to use UPDATE
FROM!
CREATE TABLE UNKNOWN1_RESERVATIONS
(ID int NOT NULL PRIMARY KEY,
NUM char(8),
BOOKED_COMMENTS_TXT varchar(200))
go
INSERT INTO UNKNOWN1_RESERVATIONS (ID, BOOKED_COMMENTS_TXT)
SELECT 1, ''RD-V0216970 RQST NON SMKING KING Unknown # : 22338921''
UNION ALL
SELECT 2, ''Received verbal authorization Authorization # 040345''
UNION ALL
SELECT 3, ''international plaza, singapore # 96722540 Not in the middle''
go
UPDATE UNKNOWN1_RESERVATIONS
SET NUM = CASE
WHEN BOOKED_COMMENTS_TXT LIKE
''%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%''
THEN SUBSTRING(BOOKED_COMMENTS_TXT,
PATINDEX(''%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'',
BOOKED_COMMENTS_TXT), 8)
ELSE NULL
END
go
SELECT * FROM UNKNOWN1_RESERVATIONS
go
DROP TABLE UNKNOWN1_RESERVATIONS
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
这篇关于关于使用patindex的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！