April 1, 2010 at 11:51 pm
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?
April 2, 2010 at 4:07 am
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
April 2, 2010 at 5:12 am
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
April 2, 2010 at 6:39 am
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
SQLkiwi blog
@SQL_Kiwi
April 2, 2010 at 7:46 am
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...
April 2, 2010 at 8:10 am
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
SQLkiwi blog
@SQL_Kiwi
April 2, 2010 at 6:41 pm
Paul,
Thanks for your reply. Your way works, but is there a way to have a "OR" in the RegEx?
1234 OR 4444
April 2, 2010 at 10:48 pm
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
April 3, 2010 at 12:54 am
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
SQLkiwi blog
@SQL_Kiwi
April 3, 2010 at 7:07 am
Thanks Guys!
I will be modifying what Paul wrote to make is a bit more dynamic and suit what my App needs.
Cheers!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply