Geek asks and answers

Regexp to find only 6 digit number in PostgreSQL text

I need to create a query to the PostgreSQL, and I need to find all text entries that contain the 6-digit number (eg 000999, 019290, 998981, 234567etc.). The problem is that the number is not necessarily at the beginning of the line or at the end of it.

I tried and did not work:

  • [0-9]{6} - returns part of a number with more than 6 digits
  • (?:(?<!\d)\d{6}(?!\d)) - postgresql does not know about lookbehind
  • [^0-9][0-9]{6}[^0-9] and variations on it, but to no avail.

Building my own Perl / C function is not really an option, as I do not have the necessary skills. Any idea that regexp can be used or other tricks that are eluding me at the moment?

EDIT

Input samples:

  • aa 0011527 /CASA → must return NOTHING
  • aa 001152/CASA → must return 001152
  • aa001152/CASA → must return 001152
  • aa0011527/CASA → must return NOTHING
  • aa001152 /CASA → must return 001152
+5
2

PostgreSQL , \b:

\b(\d{6})\b

Edit

\b PostgreSQL backspace, .

http://www.postgresql.org/docs/8.3/interactive/functions-matching.html#FUNCTIONS-POSIX-REGEXP, , \y , matches only at the beginning or end of a word,

\y(\d{6})\y

.

\m(\d{6})\M

.

regex PostgreSQL:

Escape  Description
\A      matches only at the beginning of the string (see Section 9.7.3.5 for how this differs from ^)
\m      matches only at the beginning of a word
\M      matches only at the end of a word
\y      matches only at the beginning or end of a word
\Y      matches only at a point that is not the beginning or end of a word
\Z      matches only at the end of the string (see Section 9.7.3.5 for how this differs from $)

:

:

(^|[^\d])(\d+)([^\d]|$)
+5

@h2ooooooo, :

SELECT cleantwo."ID",cleantwo."Name",cleantwo."Code"
FROM
(
SELECT cleanone."ID",cleanone."Name",unnest(cleanone."Code") as "Code" -- 3. unnest all the entries received using regexp_matches (get all combinations)
FROM 
(
SELECT sd."ID", sd."Name", regexp_matches(sd."Name", '(^|[^\d])(\d+)([^\d]|$)')
    as "Code"
FROM "T_SOME_DATA" sd
WHERE substring(sd."Name" from 1 for 15) ~('(^|[^\d])(\d+)([^\d]|$)') -- 1. get all data possible
) as cleanone
WHERE cleanone."Code" IS NOT NULL -- 2. get data where code IS NOT NULL (remove useless entries)
) as cleantwo
WHERE length(cleantwo."Code")=6 -- 4. get only the combinations relevant to my initial requirement (codes with length 6)<br/>

, , , - . !

0

More articles:


All Articles