Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

patindex - search for either value Expand / Collapse
Author
Message
Posted Thursday, April 01, 2010 11:51 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, April 04, 2010 2:00 AM
Points: 11, 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?
Post #895462
Posted Friday, April 02, 2010 4:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 9:38 AM
Points: 6,908, Visits: 12,624
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
Post #895501
Posted Friday, April 02, 2010 5:12 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, July 27, 2012 11:13 AM
Points: 88, 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
Post #895521
Posted Friday, April 02, 2010 6:39 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 11,168, Visits: 10,929
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




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #895566
Posted Friday, April 02, 2010 7:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, April 04, 2010 2:00 AM
Points: 11, 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...
Post #895615
Posted Friday, April 02, 2010 8:10 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 11,168, Visits: 10,929
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #895640
Posted Friday, April 02, 2010 6:41 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, April 04, 2010 2:00 AM
Points: 11, 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
Post #896091
Posted Friday, April 02, 2010 10:48 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, April 14, 2014 7:45 AM
Points: 3,949, Visits: 5,944
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? -- Stephen Stills
Post #896135
Posted Saturday, April 03, 2010 12:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 11,168, Visits: 10,929
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #896153
Posted Saturday, April 03, 2010 7:07 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, April 04, 2010 2:00 AM
Points: 11, 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!
Post #896228
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse