Question on using patindex

[SQL_developer]

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

[Erland Sommarskog]

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

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.

From the sample it appears that the string you are looking for is
always at the end of the comments field. In this case, this could do:

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]'
THEN right(str, 8)
ELSE NULL
END
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[SQL_developer]

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 ?

[Hugo Kornelis]

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

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 ?

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)

[Erland Sommarskog]

Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo) writes:

The reason your query failed is that you searched for 6 numeric
characters instead of 8. Changing that solves it.
Not really. Consider:

INSERT INTO UNKNOWN1_RESERVATIONS (ID, BOOKED_COMMENTS_TXT)
SELECT 1, 'RD-V02169709 RQST NON SMKING KING Unknown # : 22338921'

Do we really want the number after the RD-V?

Here is an improved version of your query - built on the assumption
that the number must be surrounded by blanks:

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 + ' ') + 1, 8)
ELSE NULL
END

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!

So what! In the general case in SQL Server there is all reason to use
FROM in UPDATE statements, since it gives you so much power and flexibility.
Sure, for this query it's an overkill, but if you think that always
using an alias is good programming style, then you need a FROM clause.
(And that is just one of the reasons that FROM with UPDATE is so good,
it permits you to specify an alias for the table being updated.)

Yeah, it's correct, if your join conditions are incomplete, your UPDATE
will be imcomplete. And I've seen more than one who had gone wrong with
his correlated subqueries. Nevermind that the performance even with
correct correlated subqueries is generally a lot poorer.

I really wish everyone would stop people from bashing people from using
FROM in UPDATE statements. This is a newsgroup for MS SQL Server. If
you don't like FROM in UPDATE clauses, go elsewhere.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Hugo Kornelis]

On Thu, 31 Mar 2005 21:26:02 +0000 (UTC), Erland Sommarskog wrote:

Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo) writes:

The reason your query failed is that you searched for 6 numeric
characters instead of 8. Changing that solves it.

Not really. Consider:

INSERT INTO UNKNOWN1_RESERVATIONS (ID, BOOKED_COMMENTS_TXT)
SELECT 1, 'RD-V02169709 RQST NON SMKING KING Unknown # : 22338921'

Do we really want the number after the RD-V?

Here is an improved version of your query - built on the assumption
that the number must be surrounded by blanks:

(snip)

Hi Erland,

Thanks for that!

I assumed that the OP wanted to use the first 8-digit number. Upon
rereading his post, I now see that he writes "exactly 8 numeric digits".

Let's hope that your assumption about the number always being surrounded
by spaces is correct. If not, a further change would be:

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][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][0-9][^0-9]%',
' ' + BOOKED_COMMENTS_TXT + ' '), 8)
ELSE NULL
END
(untested)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[SQL_developer]

Thank you for your response Erland and Hugo!

I used the UPDATE FROM statement because this code will be used as a
step in a DTS package and hence I need all the flexibility I can get :)
.. I also find that using an Alias makes it easy for me to read my code
at a later point of time.

The table I am getting my comments field from, gets updated daily and I
will be running the DTS to pick up the 8 digit number every week.
As for the number itself, it may or may not have blanks surrounding it.
I used Hugo's code and it seems to work for most cases.
There are a few cases where the comments field looks like

" Confirmation number 4234235608532, Room for 4"

So when I run this code, it picks up the first 8 digits. But I don't
want it to pick up the 8 digits in this case. It should return null
when the number is greater than 8 digits. Sorry I didn't mention this
earlier. It's just that there are so many records and I keep running
into more situations like these as I modify the code.

Thanks for all your help!

[Hugo Kornelis]

On 31 Mar 2005 14:36:23 -0800, SQL_developer wrote:

(snip)

I used Hugo's code and it seems to work for most cases.
There are a few cases where the comments field looks like

" Confirmation number 4234235608532, Room for 4"

So when I run this code, it picks up the first 8 digits. But I don't
want it to pick up the 8 digits in this case. It should return null
when the number is greater than 8 digits.

(snip)

Hi SQL_developer,

Did you test it with the code in my first post (addressed to you), or
the code in my second post (replied to Erland)? The latter *SHOULD*
exclude the example above - but I've already shut down my server, so I
can't test it now. Let me know if you saw this happening with the code
in my second post - it's probably a typo; I'll look into it tomorrow.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[SQL_developer]

Hello Hugo,

I used the code from your second post. To make it easier for you to run
it in a query analyser I will post the entire code to you.

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 # 0403455'
UNION ALL
SELECT 3, 'international plaza, singapore #96722540Not in the middle'
UNION ALL
select 4, 'Confirmation number 4234235608532, Room for 4'
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
Thanks again!

[SQL_developer]

Hello Hugo,

I used the code from your second post. To make it easier for you to run
it in a query analyser I will post the entire code to you.

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 # 0403455'
UNION ALL
SELECT 3, 'international plaza, singapore #96722540Not in the middle'
UNION ALL
select 4, 'Confirmation number 4234235608532, Room for 4'
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
Thanks again!

[Hugo Kornelis]

On 31 Mar 2005 14:53:50 -0800, SQL_developer wrote:

Hello Hugo,

I used the code from your second post. To make it easier for you to run
it in a query analyser I will post the entire code to you.

(snip)

Hi SQL_developer,

The code you posed was NOT the code from my second post; it was a mix of
code from my first and second post. I've copied the script from your
post into Query Analyzer and corrected the errors - here it is:

Note - use copy and paste to copy it to QA at your end, don't copy it by
hand. One of the errors in your version was that you changed ' ' (quote,
space, quote) to '' (quote, quote). These spaces are very significant!
Another error was that you left out the additional [^0-9] (note the
caret!!) in the LIKE and PATINDEX pattern.

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 # 0403455'
UNION ALL
SELECT 3, 'international plaza, singapore #96722540Not in the middle'
UNION ALL
select 4, 'Confirmation number 4234235608532, Room for 4'
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][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][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)

[SQL_developer]

Hi Hugo,

Thanks for your reply.

For some reason when I copy and paste data from the post to the DTS
package the Space between the Quotes disappears. That explains why I
did not have the spaces in my code.

I'm sorry about the missing Claret in my code. But I tested the latest
version and it works like a charm. Thank you so much for all your help!