PATINDEX PROBLEM

Question

Post reply

PATINDEX PROBLEM

Kirk Dudley

SSC Journeyman

Points: 92
More actions
April 7, 2009 at 1:49 pm

#73077

If I run
select patindex('%[0-9]mm%', 'RockShox Tora SL Le Femme, 100mm-travel w/lockout'),
patindex('% [0-9]mm%', 'RockShox Tora SL Le Femme, 100mm-travel w/lockout')
the first PATINDEX returns 30, as it should, however the second returns 0, which it should not.
I'm looking for the index of a space, followed by a number, followed by 'mm'. (' 100mm')
Anyone have any ideas?
:crazy:

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Kirk Dudley SSC Journeyman Points: 92 More actions · Answer 1

Nevermind...

select patindex('% [0-9][0-9][0-9]mm%', 'RockShox Tora SL Le Femme, 100mm-travel w/lockout')

:ermm:

Is there any way to check for a numeric value, no matter how many digits it has?

Sergiy SSC Guru Points: 110209 More actions · Answer 2

Using of 1st Normal Form would help a lot here.

_____________
Code for TallyGenerator

GSquared SSC Guru Points: 260824 More actions · Answer 3

I don't think patindex can do that one for you.

You could throw a multi-character wildcard in there, after the first number and before the "mm", but that might get you false positives.

You could nest multiple patindex searches, like this:

DECLARE @String VARCHAR(1000) ;

SELECT

@String = 'mary had a little 10mm, its action was smooth as ice' ;

SELECT

COALESCE(NULLIF(PATINDEX('% [0-9]mm%', @String), 0),

NULLIF(PATINDEX('% [0-9][0-9]mm%', @String), 0),

NULLIF(PATINDEX('% [0-9][0-9][0-9]mm%', @String), 0),

NULLIF(PATINDEX('% [0-9][0-9][0-9][0-9]mm%', @String), 0), 0) ;

That'll get from 0 through 9999. Add more to include longer numbers, up to the max digits you need to check for.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Kirk Dudley SSC Journeyman Points: 92 More actions · Answer 4

Sergiy (4/7/2009)
Using of 1st Normal Form would help a lot here.

External data - I get the fun job of normalizing it :w00t:

Kirk Dudley SSC Journeyman Points: 92 More actions · Answer 5

GSquared (4/7/2009)
I don't think patindex can do that one for you.
You could throw a multi-character wildcard in there, after the first number and before the "mm", but that might get you false positives.
You could nest multiple patindex searches, like this:
DECLARE @String VARCHAR(1000) ;
SELECT
@String = 'mary had a little 10mm, its action was smooth as ice' ;
SELECT
COALESCE(NULLIF(PATINDEX('% [0-9]mm%', @String), 0),
NULLIF(PATINDEX('% [0-9][0-9]mm%', @String), 0),
NULLIF(PATINDEX('% [0-9][0-9][0-9]mm%', @String), 0),
NULLIF(PATINDEX('% [0-9][0-9][0-9][0-9]mm%', @String), 0), 0) ;
That'll get from 0 through 9999. Add more to include longer numbers, up to the max digits you need to check for.

I was hoping there was a numeric only wildcard I wasn't aware of

It looks like the nested approach will have to be used.

Thanks all.

PATINDEX PROBLEM

Cookies on SQLServerCentral