关于使用patindex的问题 [英] Question on using patindex

问题描述

您好，


我正在尝试使用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

推荐答案

SQL_developer（vl * *****@gmail.com）写道：

SQL_developer (vl******@gmail.com) writes:

我正在尝试用8位数字串更新表格中的列。
8位数字是从另一列获得的是一个评论
字段。

例如

评论字段：

1）RD-V0216970 RQST NON SMKING KING未知＃：22338921
2）收到口头授权授权＃040345
3）国际广场，新加坡＃96722540

我正在使用的代码是
......
这就是我的结果集看起来像

1）V0216970
2）040345
3）96722540

但这就是我想要的结果设置看起来像

1）22338921
2）null
3）96722540

我需要的是一种限制搜索条件的方法正好是8位数字。任何建议都会有所帮助。

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
...
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.

从示例中可以看出，您要查找的字符串是

总是在评论的末尾领域。在这种情况下，这可以做到：


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， es****@sommarskog.se


适用于SQL Server SP3的联机丛书
http ：//www.microsoft.com/sql/techinf...2000/books.asp

感谢您对Erland的回复。


有时字符串位于

注释字段的中间或开头。这就是我必须选择Patindex的原因。
无论如何，我可以在代码中包含长度检查吗？

Thank your for your response Erland.

There are times when the string is in the middle or beginning of the
comments field. That''s the reason I had to go with Patindex.
Is there anyway I can include the length check into the code ?

2005年3月31日06:32:21 -0800，SQL_developer写道：

On 31 Mar 2005 06:32:21 -0800, SQL_developer wrote:

感谢您的回复Erland。

有时候字符串位于
评论字段的中间或开头。这就是我必须选择Patindex的原因。无论如何我可以在代码中包含长度检查吗？

Thank your for your response Erland.

There are times when the string is in the middle or beginning of the
comments field. That''s the reason I had to go with Patindex.
Is there anyway I can include the length check into the code ?

你好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的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！