SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


patindex - search for either value


patindex - search for either value

Author
Message
Mr Guy-323725
Mr Guy-323725
SSC Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

Group: General Forum Members
Points: 75 Visits: 45
I am looking for a pattern that will help me do the following...

I have a table with a column i want to search.

TableA
columnA
here is my phone number 12345678. but my cell is 44444444.
987654321 is my balance
i like the number 55555555. it is cool
66666666

I want to search the table and find the position of 1234% or 4444%

i was thinking patindex is correct to use here but tell me if i am wrong...

select patindex('%(1234|4444)%',PatternString)
from tableA

but this doesn't work...

this does though...

select patindex('%4444%',PatternString) from tableA
select patindex('%1234%',PatternString) from tableA

I want to get it to a single pattern. Anyone know how?
LutzM
LutzM
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23101 Visits: 13559
Something like this?

CREATE TABLE #TableA ( id INT,columnA VARCHAR(200))
INSERT INTO #TableA
SELECT 1,'here is my phone number 12345678. but my cell is 44444444.' UNION ALL
SELECT 2,'987654321 is my balance' UNION ALL
SELECT 3,'i like the number 55555555. it is cool' UNION ALL
SELECT 4,'66666666'

SELECT id, MIN(PATINDEX(n,columnA)) AS pos
FROM #TableA
CROSS APPLY
(
SELECT '%4444%' AS n UNION ALL
SELECT '%123%'
)sub
GROUP BY id
DROP TABLE #TableA





Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
vaibhav.tiwari
vaibhav.tiwari
SSC-Addicted
SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)

Group: General Forum Members
Points: 498 Visits: 334
lmu92 (4/2/2010)
Something like this?

CREATE TABLE #TableA ( id INT,columnA VARCHAR(200))
INSERT INTO #TableA
SELECT 1,'here is my phone number 12345678. but my cell is 44444444.' UNION ALL
SELECT 2,'987654321 is my balance' UNION ALL
SELECT 3,'i like the number 55555555. it is cool' UNION ALL
SELECT 4,'66666666'

SELECT id, MIN(PATINDEX(n,columnA)) AS pos
FROM #TableA
CROSS APPLY
(
SELECT '%4444%' AS n UNION ALL
SELECT '%123%'
)sub
GROUP BY id
DROP TABLE #TableA



Can you please explain what are you doing in this query...Thanx

Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34632 Visits: 11359

DECLARE @Table
TABLE (
id INTEGER IDENTITY PRIMARY KEY,
data VARCHAR(200) NOT NULL
);

INSERT @Table (data)
SELECT 'here is my phone number 12345678. but my cell is 44444444.' UNION ALL
SELECT '987651234 is my balance' UNION ALL
SELECT 'i like the number 4444444. it is cool' UNION ALL
SELECT '66666666';

SELECT id,
pos =
(
-- Find the lowest of the positions found
SELECT MIN(Positions.pos)
FROM (
-- Try to find both patterns
SELECT pos = CHARINDEX('4444', T.data)
UNION ALL
SELECT pos = CHARINDEX('1234', T.data)
) Positions
-- Exclude cases where no match ws found
WHERE Positions.pos > 0
)
FROM @Table T;



I changed the sample data a little to be more useful Doze



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Mr Guy-323725
Mr Guy-323725
SSC Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

Group: General Forum Members
Points: 75 Visits: 45
Here is a small example of what I am trying to do...


--*********
declare @String varchar(300)

set @String = 'here is my phone number 1234567890. but my cell is 4444444444, and my work number is 5555555555. i said my my phone number 1234567890. and my cell is 4444444444, and my work number is 5555555555. '
select @String

While patindex('%1234[0-9][0-9][0-9][0-9][0-9][0-9]%',@String) > 0
BEGIN
Set @String = STUFF(@String,patindex('%1234[0-9][0-9][0-9][0-9][0-9][0-9]%',@String)+4,6,REPLICATE('x',6))
select @String
End

While patindex('%4444[0-9][0-9][0-9][0-9][0-9][0-9]%',@String) > 0
BEGIN
Set @String = STUFF(@String,patindex('%1234[0-9][0-9][0-9][0-9][0-9][0-9]%',@String)+6,6,REPLICATE('x',6))
select @String
End

select @String
--**********

i want to mask the phone number... but i also dont want to have the second while loop as there could be many occurances of area codes (eg. 1234, 4444), so i wanted to see if anyone knew how to do a pattern with a logical OR (OR |) in it, to remove the second While loop??

patindex('%1234|4444[0-9][0-9][0-9][0-9][0-9][0-9]%',@String)

Thanks for the other example, but thats not what I am looking for...
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34632 Visits: 11359

DECLARE @String VARCHAR(300);

SET @String =
'here is my phone number 1234567890. but my cell is 4444444444, ' +
'and my work number is 5555555555. i said my my phone number 1234567890. ' +
'and my cell is 4444444444, and my work number is 5555555555.';

WITH Numbers (n)
AS (
-- Numbers 1...length of string
SELECT TOP (DATALENGTH(@String))
ROW_NUMBER() OVER(
ORDER BY (SELECT 0))
FROM master.sys.columns C1,
master.sys.columns C2,
master.sys.columns C3
)
SELECT @String = STUFF(@String, N.n + 4, 6, REPLICATE('X', 6))
FROM Numbers N
WHERE SUBSTRING(@String, N.n, 10) LIKE '4444[0-9][0-9][0-9][0-9][0-9][0-9]'
OR SUBSTRING(@String, N.n, 10) LIKE '1234[0-9][0-9][0-9][0-9][0-9][0-9]';

SELECT @String;





Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Mr Guy-323725
Mr Guy-323725
SSC Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

Group: General Forum Members
Points: 75 Visits: 45
Paul,

Thanks for your reply. Your way works, but is there a way to have a "OR" in the RegEx?

1234 OR 4444
The Dixie Flatline
The Dixie Flatline
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12584 Visits: 6903
In books online (under "LIKE Comparisons") I found no syntax to support an OR. Nothing under the index topics of PATINDEX or WILDCARD, either.

ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/581fb289-29f9-412b-869c-18d33a9e93d5.htm

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34632 Visits: 11359
Mr Guy-323725 (4/2/2010)
Thanks for your reply. Your way works, but is there a way to have a "OR" in the RegEx?

It is not a regular expression, and no there is not an explicit OR.
If you need full regular expressions, these are available via SQLCLR integration.
Test the performance of the method I posted, you will find it t be very respectable.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Mr Guy-323725
Mr Guy-323725
SSC Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

Group: General Forum Members
Points: 75 Visits: 45
Thanks Guys!

I will be modifying what Paul wrote to make is a bit more dynamic and suit what my App needs.

Cheers!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search