Simple regex to filter out pre and suffix characters
I have a field in my database which has a long list of strings separated by commas. Here are few row examples:
HAB
DHAB,RAB,DAB
HAB,RAB,DAB
RAB,HAB,
RAB,HAB,DAB
My query has the following condition:
WHERE description LIKE '%HAB%'
But it returns the second row which has 'DHAB'.
Can it be done using regex with the WHERE statement so that I only get entries which have 'HAB' in the list (one string) and not the entries with 'DHAB'?
sql arrays regex postgresql database-design
edited Nov 14 '18 at 2:26
Erwin Brandstetter
343k65628804
asked Nov 13 '18 at 18:46
oooooo
445
add a comment |
I have a field in my database which has a long list of strings separated by commas. Here are few row examples:
HAB
DHAB,RAB,DAB
HAB,RAB,DAB
RAB,HAB,
RAB,HAB,DAB
My query has the following condition:
WHERE description LIKE '%HAB%'
But it returns the second row which has 'DHAB'.
Can it be done using regex with the WHERE statement so that I only get entries which have 'HAB' in the list (one string) and not the entries with 'DHAB'?
sql arrays regex postgresql database-design
edited Nov 14 '18 at 2:26
Erwin Brandstetter
343k65628804
asked Nov 13 '18 at 18:46
oooooo
445
You may useWHERE description ~ '(^|,)HAB($|,)'
– Wiktor Stribiżew
Nov 13 '18 at 18:49
Thank you for the response. It is working on the database level. But for some when I test it in regexr.com, it does not include the case where HAB is at the begining of the line or at the end of the line. Is there anything I should be aware of?
– ooo
Nov 13 '18 at 19:23
You are testing against a single line with linebreaks there, enablemmodifier.
– Wiktor Stribiżew
Nov 13 '18 at 19:28
Sorry. I was testing on a string that had multiple n in it. my bad.
– ooo
Nov 13 '18 at 19:29
Yes I will, thank you.
– ooo
Nov 13 '18 at 20:00
add a comment |
I have a field in my database which has a long list of strings separated by commas. Here are few row examples:
HAB
DHAB,RAB,DAB
HAB,RAB,DAB
RAB,HAB,
RAB,HAB,DAB
My query has the following condition:
WHERE description LIKE '%HAB%'
But it returns the second row which has 'DHAB'.
Can it be done using regex with the WHERE statement so that I only get entries which have 'HAB' in the list (one string) and not the entries with 'DHAB'?
sql arrays regex postgresql database-design
edited Nov 14 '18 at 2:26
Erwin Brandstetter
343k65628804
asked Nov 13 '18 at 18:46
oooooo
445
I have a field in my database which has a long list of strings separated by commas. Here are few row examples:
HAB
DHAB,RAB,DAB
HAB,RAB,DAB
RAB,HAB,
RAB,HAB,DAB
My query has the following condition:
WHERE description LIKE '%HAB%'
But it returns the second row which has 'DHAB'.
Can it be done using regex with the WHERE statement so that I only get entries which have 'HAB' in the list (one string) and not the entries with 'DHAB'?
sql arrays regex postgresql database-design
sql arrays regex postgresql database-design
edited Nov 14 '18 at 2:26
Erwin Brandstetter
343k65628804
asked Nov 13 '18 at 18:46
oooooo
445
edited Nov 14 '18 at 2:26
Erwin Brandstetter
343k65628804
asked Nov 13 '18 at 18:46
oooooo
445
edited Nov 14 '18 at 2:26
Erwin Brandstetter
343k65628804
edited Nov 14 '18 at 2:26
Erwin Brandstetter
343k65628804
edited Nov 14 '18 at 2:26
Erwin Brandstetter
343k65628804
343k65628804
asked Nov 13 '18 at 18:46
oooooo
445
asked Nov 13 '18 at 18:46
oooooo
445
asked Nov 13 '18 at 18:46
oooooo
445
445
You may useWHERE description ~ '(^|,)HAB($|,)'
– Wiktor Stribiżew
Nov 13 '18 at 18:49
Thank you for the response. It is working on the database level. But for some when I test it in regexr.com, it does not include the case where HAB is at the begining of the line or at the end of the line. Is there anything I should be aware of?
– ooo
Nov 13 '18 at 19:23
You are testing against a single line with linebreaks there, enablemmodifier.
– Wiktor Stribiżew
Nov 13 '18 at 19:28
Sorry. I was testing on a string that had multiple n in it. my bad.
– ooo
Nov 13 '18 at 19:29
Yes I will, thank you.
– ooo
Nov 13 '18 at 20:00
add a comment |
You may useWHERE description ~ '(^|,)HAB($|,)'
– Wiktor Stribiżew
Nov 13 '18 at 18:49
Thank you for the response. It is working on the database level. But for some when I test it in regexr.com, it does not include the case where HAB is at the begining of the line or at the end of the line. Is there anything I should be aware of?
– ooo
Nov 13 '18 at 19:23
You are testing against a single line with linebreaks there, enablemmodifier.
– Wiktor Stribiżew
Nov 13 '18 at 19:28
Sorry. I was testing on a string that had multiple n in it. my bad.
– ooo
Nov 13 '18 at 19:29
Yes I will, thank you.
– ooo
Nov 13 '18 at 20:00
You may use
WHERE description ~ '(^|,)HAB($|,)'– Wiktor Stribiżew
Nov 13 '18 at 18:49
You may use
WHERE description ~ '(^|,)HAB($|,)'– Wiktor Stribiżew
Nov 13 '18 at 18:49
Thank you for the response. It is working on the database level. But for some when I test it in regexr.com, it does not include the case where HAB is at the begining of the line or at the end of the line. Is there anything I should be aware of?
– ooo
Nov 13 '18 at 19:23
Thank you for the response. It is working on the database level. But for some when I test it in regexr.com, it does not include the case where HAB is at the begining of the line or at the end of the line. Is there anything I should be aware of?
– ooo
Nov 13 '18 at 19:23
You are testing against a single line with linebreaks there, enable
m modifier.– Wiktor Stribiżew
Nov 13 '18 at 19:28
You are testing against a single line with linebreaks there, enable
m modifier.– Wiktor Stribiżew
Nov 13 '18 at 19:28
Sorry. I was testing on a string that had multiple n in it. my bad.
– ooo
Nov 13 '18 at 19:29
Sorry. I was testing on a string that had multiple n in it. my bad.
– ooo
Nov 13 '18 at 19:29
Yes I will, thank you.
– ooo
Nov 13 '18 at 20:00
Yes I will, thank you.
– ooo
Nov 13 '18 at 20:00
add a comment |
2 Answers
2
active
oldest
votes
You may use
WHERE description ~ '(^|,)HAB($|,)'
The regex matches
(^|,)- start of string or a,
HAB- literal substring
($|,)- end of string or,
See the online regex demo.
answered Nov 13 '18 at 19:26
Wiktor StribiżewWiktor Stribiżew
313k16133210
add a comment |
Regular expressions are powerful and versatile, but also expensive. Consider a different approach: transform the list to an actual array with string_to_array() and then:
WHERE 'HAB' = ANY (string_to_array(description, ',')
Or:
WHERE string_to_array(description, ',') @> '{HAB}'
db<>fiddle here
The latter can be supported with a GIN index, which makes it faster by orders of magnitude for big tables.
CREATE INDEX ON tbl USING gin (string_to_array(description, ','));
Related:
- Can PostgreSQL index array columns?
Or consider a normalized DB design replacing the comma-separated values with a 1:n relationship. Related:
- How to implement a many-to-many relationship in PostgreSQL?
- Can PostgreSQL have a uniqueness constraint on array elements?
answered Nov 14 '18 at 2:23
Erwin BrandstetterErwin Brandstetter
343k65628804
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53287634%2fsimple-regex-to-filter-out-pre-and-suffix-characters%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
You may use
WHERE description ~ '(^|,)HAB($|,)'
The regex matches
(^|,)- start of string or a,
HAB- literal substring
($|,)- end of string or,
See the online regex demo.
answered Nov 13 '18 at 19:26
Wiktor StribiżewWiktor Stribiżew
313k16133210
add a comment |
You may use
WHERE description ~ '(^|,)HAB($|,)'
The regex matches
(^|,)- start of string or a,
HAB- literal substring
($|,)- end of string or,
See the online regex demo.
answered Nov 13 '18 at 19:26
Wiktor StribiżewWiktor Stribiżew
313k16133210
add a comment |
You may use
WHERE description ~ '(^|,)HAB($|,)'
The regex matches
(^|,)- start of string or a,
HAB- literal substring
($|,)- end of string or,
See the online regex demo.
answered Nov 13 '18 at 19:26
Wiktor StribiżewWiktor Stribiżew
313k16133210
You may use
WHERE description ~ '(^|,)HAB($|,)'
The regex matches
(^|,)- start of string or a,
HAB- literal substring
($|,)- end of string or,
See the online regex demo.
answered Nov 13 '18 at 19:26
Wiktor StribiżewWiktor Stribiżew
313k16133210
answered Nov 13 '18 at 19:26
Wiktor StribiżewWiktor Stribiżew
313k16133210
answered Nov 13 '18 at 19:26
Wiktor StribiżewWiktor Stribiżew
313k16133210
answered Nov 13 '18 at 19:26
Wiktor StribiżewWiktor Stribiżew
313k16133210
313k16133210
add a comment |
add a comment |
Regular expressions are powerful and versatile, but also expensive. Consider a different approach: transform the list to an actual array with string_to_array() and then:
WHERE 'HAB' = ANY (string_to_array(description, ',')
Or:
WHERE string_to_array(description, ',') @> '{HAB}'
db<>fiddle here
The latter can be supported with a GIN index, which makes it faster by orders of magnitude for big tables.
CREATE INDEX ON tbl USING gin (string_to_array(description, ','));
Related:
- Can PostgreSQL index array columns?
Or consider a normalized DB design replacing the comma-separated values with a 1:n relationship. Related:
- How to implement a many-to-many relationship in PostgreSQL?
- Can PostgreSQL have a uniqueness constraint on array elements?
answered Nov 14 '18 at 2:23
Erwin BrandstetterErwin Brandstetter
343k65628804
add a comment |
Regular expressions are powerful and versatile, but also expensive. Consider a different approach: transform the list to an actual array with string_to_array() and then:
WHERE 'HAB' = ANY (string_to_array(description, ',')
Or:
WHERE string_to_array(description, ',') @> '{HAB}'
db<>fiddle here
The latter can be supported with a GIN index, which makes it faster by orders of magnitude for big tables.
CREATE INDEX ON tbl USING gin (string_to_array(description, ','));
Related:
- Can PostgreSQL index array columns?
Or consider a normalized DB design replacing the comma-separated values with a 1:n relationship. Related:
- How to implement a many-to-many relationship in PostgreSQL?
- Can PostgreSQL have a uniqueness constraint on array elements?
answered Nov 14 '18 at 2:23
Erwin BrandstetterErwin Brandstetter
343k65628804
add a comment |
Regular expressions are powerful and versatile, but also expensive. Consider a different approach: transform the list to an actual array with string_to_array() and then:
WHERE 'HAB' = ANY (string_to_array(description, ',')
Or:
WHERE string_to_array(description, ',') @> '{HAB}'
db<>fiddle here
The latter can be supported with a GIN index, which makes it faster by orders of magnitude for big tables.
CREATE INDEX ON tbl USING gin (string_to_array(description, ','));
Related:
- Can PostgreSQL index array columns?
Or consider a normalized DB design replacing the comma-separated values with a 1:n relationship. Related:
- How to implement a many-to-many relationship in PostgreSQL?
- Can PostgreSQL have a uniqueness constraint on array elements?
answered Nov 14 '18 at 2:23
Erwin BrandstetterErwin Brandstetter
343k65628804
Regular expressions are powerful and versatile, but also expensive. Consider a different approach: transform the list to an actual array with string_to_array() and then:
WHERE 'HAB' = ANY (string_to_array(description, ',')
Or:
WHERE string_to_array(description, ',') @> '{HAB}'
db<>fiddle here
The latter can be supported with a GIN index, which makes it faster by orders of magnitude for big tables.
CREATE INDEX ON tbl USING gin (string_to_array(description, ','));
Related:
- Can PostgreSQL index array columns?
Or consider a normalized DB design replacing the comma-separated values with a 1:n relationship. Related:
- How to implement a many-to-many relationship in PostgreSQL?
- Can PostgreSQL have a uniqueness constraint on array elements?
answered Nov 14 '18 at 2:23
Erwin BrandstetterErwin Brandstetter
343k65628804
answered Nov 14 '18 at 2:23
Erwin BrandstetterErwin Brandstetter
343k65628804
answered Nov 14 '18 at 2:23
Erwin BrandstetterErwin Brandstetter
343k65628804
answered Nov 14 '18 at 2:23
Erwin BrandstetterErwin Brandstetter
343k65628804
343k65628804
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53287634%2fsimple-regex-to-filter-out-pre-and-suffix-characters%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
You may use
WHERE description ~ '(^|,)HAB($|,)'– Wiktor Stribiżew
Nov 13 '18 at 18:49
Thank you for the response. It is working on the database level. But for some when I test it in regexr.com, it does not include the case where HAB is at the begining of the line or at the end of the line. Is there anything I should be aware of?
– ooo
Nov 13 '18 at 19:23
You are testing against a single line with linebreaks there, enable
mmodifier.– Wiktor Stribiżew
Nov 13 '18 at 19:28
Sorry. I was testing on a string that had multiple n in it. my bad.
– ooo
Nov 13 '18 at 19:29
Yes I will, thank you.
– ooo
Nov 13 '18 at 20:00