PATINDEX to Retrieve data from text field

I am trying to retrieve the data from a table that has text datatype. I just
need to
pull 5 digit numeric value from there which can later be matched with
another table
that has this 5 digit key. Let us assume the table name is Tab1. There are
two
columns col1 containing RecordId and col2 containing text data. I have
created some dummy data to explain my needs:
Col1 Col2
1001 @.:92A:CUSTOMER1@.:97D://XX022211221@.CUSTOMER NAME IS ABC INC.@.SOMETHING
1002 @.:92A:CUSTOMER@.:97A://XX022311229@.CLIENT NAME IS DEF INC.@.SOMETHING
1003 @.:92A:CUST4@.:97B:/XX022511233@.CLIENT NAME IS GHI INC.@.ANYTHING
1004 @.:92A:CUST8@.:97C:XX022311333@.CLIENT NAME IS LKM INC.@.ANYTHING
1005 @.:92A:CUST8@.:97D:22333@.CLIENT NAME IS NOP INC.@.SOMETHING
1006 @.:92A:CUST8@.:97C:CLIENT NAME IS QRS INC.@.ANYTHING
1007 @.:92A:CUST8@.ANYTHING
If I use the following Query which needs to be tuned up to get the right
resultset:
SELECT SUBSTRING(col2, PATINDEX('%@.:97_:%', col2)+14, 5)
from tab1
where PATINDEX('%@.:97_:%', col2) > 0
I get the following results: The top two results are correct but others are
not.
col1 col2
-- --
1001 11221
1002 11229
1003 1233@.
1004 333@.C
1005 IENT
1006 AME I
I need the following resultset from the above data:
Col1 Col2
-- --
1001 11221
1002 11229
1003 11233
1004 11333
1005 22333
Col1 Id 1006 does not have the 5 digit numeric value so it is not required
in the
resultset. Id 1007 does not have :97_C: so this is also not required in the
resultset
too. I will appreciate your help. Thanks in advance. Fraz
Fraz
Look at this example helps you
CREATE FUNCTION dbo.CleanChars
(@.str VARCHAR(8000), @.validchars VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
WHILE PATINDEX('%[^' + @.validchars + ']%',@.str) > 0
SET @.str=REPLACE(@.str, SUBSTRING(@.str ,PATINDEX('%[^' + @.validchars +
']%',@.str), 1) ,'')
RETURN @.str
END
GO
CREATE TABLE sometable
(namestr VARCHAR(20) PRIMARY KEY)
INSERT INTO sometable VALUES ('AB-C123')
INSERT INTO sometable VALUES ('A,B,C')
SELECT namestr,
dbo.CleanChars(namestr,'A-Z 0-9')
FROM sometable
drop table sometable
drop function dbo.CleanChars
"Fraz" <Fraz@.discussions.microsoft.com> wrote in message
news:B964C72E-D1A4-4906-A105-E1D87A2F29D6@.microsoft.com...
> I am trying to retrieve the data from a table that has text datatype. I
just
> need to
> pull 5 digit numeric value from there which can later be matched with
> another table
> that has this 5 digit key. Let us assume the table name is Tab1. There are
> two
> columns col1 containing RecordId and col2 containing text data. I have
> created some dummy data to explain my needs:
> Col1 Col2
> 1001 @.:92A:CUSTOMER1@.:97D://XX022211221@.CUSTOMER NAME IS ABC
INC.@.SOMETHING
> 1002 @.:92A:CUSTOMER@.:97A://XX022311229@.CLIENT NAME IS DEF
INC.@.SOMETHING
> 1003 @.:92A:CUST4@.:97B:/XX022511233@.CLIENT NAME IS GHI INC.@.ANYTHING
> 1004 @.:92A:CUST8@.:97C:XX022311333@.CLIENT NAME IS LKM INC.@.ANYTHING
> 1005 @.:92A:CUST8@.:97D:22333@.CLIENT NAME IS NOP INC.@.SOMETHING
> 1006 @.:92A:CUST8@.:97C:CLIENT NAME IS QRS INC.@.ANYTHING
> 1007 @.:92A:CUST8@.ANYTHING
> If I use the following Query which needs to be tuned up to get the right
> resultset:
> SELECT SUBSTRING(col2, PATINDEX('%@.:97_:%', col2)+14, 5)
> from tab1
> where PATINDEX('%@.:97_:%', col2) > 0
> I get the following results: The top two results are correct but others
are
> not.
> col1 col2
> -- --
> 1001 11221
> 1002 11229
> 1003 1233@.
> 1004 333@.C
> 1005 IENT
> 1006 AME I
> I need the following resultset from the above data:
> Col1 Col2
> -- --
> 1001 11221
> 1002 11229
> 1003 11233
> 1004 11333
> 1005 22333
> Col1 Id 1006 does not have the 5 digit numeric value so it is not required
> in the
> resultset. Id 1007 does not have :97_C: so this is also not required in
the
> resultset
> too. I will appreciate your help. Thanks in advance. Fraz
|||Uri: Thanks for your valuable input. This is a nice function which I am
trying to see if it can fit in my needs. If you could help little more by
showing how I can check to see the 5 digit numbers (11233) between this data
@.:97B:/XX022511233@.CLIENT. The position is always not the same. So by getting
@.:97_: we can get first position and by next "@." we can get second position.
Now I know that my data is in between first and second position and by using
RIGHT function I can get the 5 right digits. Thanks again...Fraz
"Uri Dimant" wrote:

> Fraz
> Look at this example helps you
> CREATE FUNCTION dbo.CleanChars
> (@.str VARCHAR(8000), @.validchars VARCHAR(8000))
> RETURNS VARCHAR(8000)
> BEGIN
> WHILE PATINDEX('%[^' + @.validchars + ']%',@.str) > 0
> SET @.str=REPLACE(@.str, SUBSTRING(@.str ,PATINDEX('%[^' + @.validchars +
> ']%',@.str), 1) ,'')
> RETURN @.str
> END
> GO
> CREATE TABLE sometable
> (namestr VARCHAR(20) PRIMARY KEY)
> INSERT INTO sometable VALUES ('AB-C123')
> INSERT INTO sometable VALUES ('A,B,C')
> SELECT namestr,
> dbo.CleanChars(namestr,'A-Z 0-9')
> FROM sometable
>
> drop table sometable
> drop function dbo.CleanChars
> "Fraz" <Fraz@.discussions.microsoft.com> wrote in message
> news:B964C72E-D1A4-4906-A105-E1D87A2F29D6@.microsoft.com...
> just
> INC.@.SOMETHING
> INC.@.SOMETHING
> are
> the
>
>
|||Hi Fraz,
On Thu, 14 Apr 2005 06:30:05 -0700, Fraz wrote:
(snip)
>I need the following resultset from the above data:
>Col1 Col2
>-- --
>1001 11221
>1002 11229
>1003 11233
>1004 11333
>1005 22333
(snip)
Try the following (note: I added a test case to check that I return the
five digits preceding "@." AFTER the "@.:97_:" marker, not simply the
first five digits followed by "@.").
-- Set up test table and fill it with some rows
create table tab1 (col1 int not null primary key, col2 varchar(200))
go
insert into tab1
select 1001, '@.:92A:CUSTOMER1@.:97D://XX022211221@.CUSTOMER NAME IS ABC
INC.@.SOMETHING'
union all
select 1002, '@.:92A:CUSTOMER@.:97A://XX022311229@.CLIENT NAME IS DEF
INC.@.SOMETHING'
union all
select 1003, '@.:92A:CUST4@.:97B:/XX022511233@.CLIENT NAME IS GHI
INC.@.ANYTHING'
union all
select 1004, '@.:92A:CUST8@.:97C:XX022311333@.CLIENT NAME IS LKM
INC.@.ANYTHING'
union all
select 1005, '@.:92A:CUST8@.:97D:22333@.CLIENT NAME IS NOP INC.@.SOMETHING'
union all
select 1006, '@.:92A:CUST8@.:97C:CLIENT NAME IS QRS INC.@.ANYTHING'
union all
select 1007, '@.:92A:CUST8@.ANYTHING'
union all
select 1008, '@.:92A:CUST44444@.:97B:/XX022511233@.CLIENT NAME IS GHI
INC.@.ANYTHING'
go
-- Here's the code:
SELECT col1,
SUBSTRING(col2,
PATINDEX('%[0-9][0-9][0-9][0-9][0-9]@.%',
SUBSTRING(col2,
PATINDEX('%@.:97_:%', col2),
LEN(col2)))
+ PATINDEX('%@.:97_:%', col2)
- 1,
5)
FROM tab1
WHERE col2 LIKE '%@.:97_:%[0-9][0-9][0-9][0-9][0-9]@.%'
go
-- Done. Now cleanup.
drop table tab1
go
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hello Hugo,
Your code has worked excellently. Most of the 5 digit numbers were correct
except for a few records that were very long and numbers were not correct. I
have dealt with it separately. Thanks a lot for your help. Cheers... Fraz
"Hugo Kornelis" wrote:

> Hi Fraz,
> On Thu, 14 Apr 2005 06:30:05 -0700, Fraz wrote:
> (snip)
> (snip)
> Try the following (note: I added a test case to check that I return the
> five digits preceding "@." AFTER the "@.:97_:" marker, not simply the
> first five digits followed by "@.").
> -- Set up test table and fill it with some rows
> create table tab1 (col1 int not null primary key, col2 varchar(200))
> go
> insert into tab1
> select 1001, '@.:92A:CUSTOMER1@.:97D://XX022211221@.CUSTOMER NAME IS ABC
> INC.@.SOMETHING'
> union all
> select 1002, '@.:92A:CUSTOMER@.:97A://XX022311229@.CLIENT NAME IS DEF
> INC.@.SOMETHING'
> union all
> select 1003, '@.:92A:CUST4@.:97B:/XX022511233@.CLIENT NAME IS GHI
> INC.@.ANYTHING'
> union all
> select 1004, '@.:92A:CUST8@.:97C:XX022311333@.CLIENT NAME IS LKM
> INC.@.ANYTHING'
> union all
> select 1005, '@.:92A:CUST8@.:97D:22333@.CLIENT NAME IS NOP INC.@.SOMETHING'
> union all
> select 1006, '@.:92A:CUST8@.:97C:CLIENT NAME IS QRS INC.@.ANYTHING'
> union all
> select 1007, '@.:92A:CUST8@.ANYTHING'
> union all
> select 1008, '@.:92A:CUST44444@.:97B:/XX022511233@.CLIENT NAME IS GHI
> INC.@.ANYTHING'
> go
> -- Here's the code:
> SELECT col1,
> SUBSTRING(col2,
> PATINDEX('%[0-9][0-9][0-9][0-9][0-9]@.%',
> SUBSTRING(col2,
> PATINDEX('%@.:97_:%', col2),
> LEN(col2)))
> + PATINDEX('%@.:97_:%', col2)
> - 1,
> 5)
> FROM tab1
> WHERE col2 LIKE '%@.:97_:%[0-9][0-9][0-9][0-9][0-9]@.%'
> go
>
> -- Done. Now cleanup.
> drop table tab1
> go
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Fri, 22 Apr 2005 14:21:03 -0700, Fraz wrote:

>Hello Hugo,
>Your code has worked excellently. Most of the 5 digit numbers were correct
>except for a few records that were very long and numbers were not correct. I
>have dealt with it separately. Thanks a lot for your help. Cheers... Fraz
Hi Fraz,
Good to hear that it worked for you. Thanks for reporting back!
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)