Select a strings length containing a range of numerical values that follow a letter

[RedUser77]

Hello!

I am having trouble figuring out how to select a certain column whose string starts with the letter C and ends with 8-10 numbers only. I know how to use 'C%' but am unable on how to figure out the string length in range part. Below I have it pulling anything from address that start with C. I just need to figure out how to implement the range of 8-10 numbers that follow that C into the sample code below.

Any ideas?

SELECT c.NEIGHBORS,
c.COUNTRY,
c.ADDRESS,
FROM DATA.SAMPLE a
LEFT JOIN DATA.SAMPLE_DIM b ON (a.CAR = b.CAR)
LEFT JOIN DATA.EXTRA c ON (a.COLOR = c.COLOR)
WHERE a.CAR LIKE 'Toyota'  AND c.INTERNET = 'Charter' AND c.ADDRESS LIKE 'C%';
QUIT;

[ballardw]

The COUNTC function can count the number of specific characters:

The function searches in the first parameter, the string to search, for the characters in the second position. The 'd' add all the digits so is just quicker than typing all the 0 through 9.

Caveat: this will count the digits even if there are spaces or other letters in the middle. Since you haven't provided an actual examples of the string to search I am starting with a simple case. If you have other stuff you need to provide examples and which match your rule(s).

data example;
   input str :$15.;
   nums = countc(str,'1','d');
datalines;
C1
C22
C345
C444
C4567
C12345678
C1234566788
C1111111111
;

---

@RedUser77 wrote:

Hello!

I am having trouble figuring out how to select a certain column whose string starts with the letter C and ends with 8-10 numbers only. I know how to use 'C%' but am unable on how to figure out the string length in range part. Below I have it pulling anything from address that start with C. I just need to figure out how to implement the range of 8-10 numbers that follow that C into the sample code below.

Any ideas?

SELECT c.NEIGHBORS,
c.COUNTRY,
c.ADDRESS,
FROM DATA.SAMPLE a
LEFT JOIN DATA.SAMPLE_DIM b ON (a.CAR = b.CAR)
LEFT JOIN DATA.EXTRA c ON (a.COLOR = c.COLOR)
WHERE a.CAR LIKE 'Toyota'  AND c.INTERNET = 'Charter' AND c.ADDRESS LIKE 'C%';
QUIT;

---

[RedUser77]

This column, c.ADDRESS, has data populated in it such as 'D12345', 'C1234567', 'C12345678', 'C123456789', 'W123', 'CBDUEJDKS', etc. I want it to only return strings that start with C and have 8-10 numbers that follow. So, from this list, it should return C1234567, C12345678, and C123456789 only. Is there a way to implement this into the PROC SQL statement originally posted?

[andreas_lds · Accepted solution]

Untested:

and prxmatch('/C\d{8,10}/', c.address)

[RedUser77]

Thank you. Now, this does work if I were to use this statement three times on two other columns following the same format? I have two other columns I want to pull where data starts with C and has anywhere from 8-10 digits following. Would I just put the whole statement in () with OR inside?

Like: AND (PRXMATCH('/C\d{8,10}/', c.ADDRESS) OR PRXMATCH('/C\d{8,10}/', c.ADDRESS_1) OR PRXMATCH('/C\d{8,10}/', c.ADDRESS_2)) 

[andreas_lds]

Yes.

[Ksharp]

data example;
   input str $15.;
   if prxmatch('/^C\d{8,10}$/i',strip(str)) then flag=1;
datalines;
C1
C22
C345
C444
C4567
C12345678
C1234566788
C1111111111
;