Styjun

How to draw this figure using Tikz?

The quicker I go up, the sooner I’ll go down - Riddle

What causes the traces to wrinkle like this and should I be worried

Why does NASA publish all the results/data it gets?

Guitar tuning (EADGBE), "perfect" fourths?

Has my MacBook been hacked?

Will Proving or Disproving of any of the following have effects on Chemistry in general?

Is it true that, "just ten trading days represent 63 per cent of the returns of the past 50 years"?

Where Does VDD+0.3V Input Limit Come From on IC chips?

Resolving moral conflict

My 15 year old son is gay. How do I express my feelings about this?

What are the benefits and disadvantages if a creature has multiple tails, e.g., Kyuubi or Nekomata?

What is the need of methods like GET and POST in the HTTP protocol?

Why is there not a feasible solution for a MIP?

Does wetting a beer glass change the foam characteristics?

Is it a good idea to leave minor world details to the reader's imagination?

Worms crawling under skin

How can an attacker use robots.txt?

1, 2, 4, 8, 16, ... 33?

Meaning of 'ran' in German?

How much damage can be done just by heating matter?

Which museums have artworks of all four Ninja Turtles' namesakes?

Past tense of "greenlight"

Late 1970's and 6502 chip facilities for operating systems

regular expression replace for SQL

Is there a regular expression to detect a valid regular expression?jQuery selector regular expressionsHow to validate an email address using a regular expression?Regular Expression for alphanumeric and underscoresRegular expression to match a line that doesn't contain a wordHow do you access the matched groups in a JavaScript regular expression?Regular Expressions: Is there an AND operator?How do you use a variable in a regular expression?How to do a regular expression replace in MySQL?How to replace all occurrences of a string?

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

0

I have to replace a string pattern in SQL with empty string, could anyone please suggest me?

Input String 'AC001,AD001,AE001,SA001,AE002,SD001'

Output String 'AE001,AE002

There are the 4 digit codes with first 2 characters "alphabets" and last two are digits. This is always a 4 digit code. And I have to replace all codes except the codes starting with "AE".

I can have 0 or more instances of "AE" codes in the string. The final output should be a formatted string "separated by commas" for multiple "AE" codes as mentioned above.

regex postgresql

share|improve this question

edited Mar 28 at 19:31

a_horse_with_no_name

333k5555 gold badges518518 silver badges610610 bronze badges

asked Mar 28 at 16:09

IssaqIssaq

2166 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Have you looked at the Postgres manual page on regular expressions? It includes a regexp_replace function.
  
  – IMSoP
  Mar 28 at 16:15
  

  
  
  
  

add a comment
 | 

0

I have to replace a string pattern in SQL with empty string, could anyone please suggest me?

Input String 'AC001,AD001,AE001,SA001,AE002,SD001'

Output String 'AE001,AE002

There are the 4 digit codes with first 2 characters "alphabets" and last two are digits. This is always a 4 digit code. And I have to replace all codes except the codes starting with "AE".

I can have 0 or more instances of "AE" codes in the string. The final output should be a formatted string "separated by commas" for multiple "AE" codes as mentioned above.

regex postgresql

share|improve this question

edited Mar 28 at 19:31

a_horse_with_no_name

333k5555 gold badges518518 silver badges610610 bronze badges

asked Mar 28 at 16:09

IssaqIssaq

2166 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Have you looked at the Postgres manual page on regular expressions? It includes a regexp_replace function.
  
  – IMSoP
  Mar 28 at 16:15
  

  
  
  
  

add a comment
 | 

I have to replace a string pattern in SQL with empty string, could anyone please suggest me?

Input String 'AC001,AD001,AE001,SA001,AE002,SD001'

Output String 'AE001,AE002

There are the 4 digit codes with first 2 characters "alphabets" and last two are digits. This is always a 4 digit code. And I have to replace all codes except the codes starting with "AE".

I can have 0 or more instances of "AE" codes in the string. The final output should be a formatted string "separated by commas" for multiple "AE" codes as mentioned above.

regex postgresql

share|improve this question

edited Mar 28 at 19:31

a_horse_with_no_name

333k5555 gold badges518518 silver badges610610 bronze badges

asked Mar 28 at 16:09

IssaqIssaq

2166 bronze badges

share|improve this question

edited Mar 28 at 19:31

a_horse_with_no_name

333k5555 gold badges518518 silver badges610610 bronze badges

asked Mar 28 at 16:09

IssaqIssaq

2166 bronze badges

share|improve this question

edited Mar 28 at 19:31

a_horse_with_no_name

333k5555 gold badges518518 silver badges610610 bronze badges

asked Mar 28 at 16:09

IssaqIssaq

2166 bronze badges

edited Mar 28 at 19:31

a_horse_with_no_name

333k5555 gold badges518518 silver badges610610 bronze badges

edited Mar 28 at 19:31

a_horse_with_no_name

333k5555 gold badges518518 silver badges610610 bronze badges

asked Mar 28 at 16:09

IssaqIssaq

2166 bronze badges

asked Mar 28 at 16:09

IssaqIssaq

2166 bronze badges

2 Answers
2

active

oldest

votes

0

Here is one option calling regex_replace multiple times, eliminating the "not required" strings little by little in each iteration to arrive at the required output.

SELECT regexp_replace(
 regexp_replace(
 regexp_replace(
 'AC001,AD001,AE001,SA001,AE002,SD001', '(?<!AE)d3,0,1', 'X','g'
 ),'..X','','g'
 ),',$','','g'
 )

See Demo here

share|improve this answer

answered Mar 28 at 16:59

vmarolivmaroli

54088 silver badges1616 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you much vmaroli your solution really worked. I didn't understand the use of "0,1 in the code. Could you please explain?
  
  – Issaq
  Mar 28 at 17:25
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  ,0,1 is for matching zero or one coma character.
  
  – vmaroli
  Mar 29 at 0:57
  
  

  
  
  
  

add a comment
 | 

0

I would convert the list to an array, unnest that to rows then filter out those that should be kept and aggregate it back to a string:

select string_agg(t, ',')
from unnest(string_to_array('AC001,AD001,AE001,SA001,AE002,SD001',',') as x(t)
where x.t like 'AE%'; --<< only keep those

This is independent of the number of elements in the string and can easily be extended to support more complex conditions.

---

This is a good example why storing comma separated values in a single column is not such a good idea to begin with.

share|improve this answer

answered Mar 28 at 19:30

a_horse_with_no_namea_horse_with_no_name

333k5555 gold badges518518 silver badges610610 bronze badges

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/4.0/"u003ecc by-sa 4.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
);



);

draft saved

draft discarded

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

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55402254%2fregular-expression-replace-for-sql%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

Here is one option calling regex_replace multiple times, eliminating the "not required" strings little by little in each iteration to arrive at the required output.

SELECT regexp_replace(
 regexp_replace(
 regexp_replace(
 'AC001,AD001,AE001,SA001,AE002,SD001', '(?<!AE)d3,0,1', 'X','g'
 ),'..X','','g'
 ),',$','','g'
 )

See Demo here

share|improve this answer

answered Mar 28 at 16:59

vmarolivmaroli

54088 silver badges1616 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you much vmaroli your solution really worked. I didn't understand the use of "0,1 in the code. Could you please explain?
  
  – Issaq
  Mar 28 at 17:25
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  ,0,1 is for matching zero or one coma character.
  
  – vmaroli
  Mar 29 at 0:57
  
  

  
  
  
  

add a comment
 | 

0

Here is one option calling regex_replace multiple times, eliminating the "not required" strings little by little in each iteration to arrive at the required output.

SELECT regexp_replace(
 regexp_replace(
 regexp_replace(
 'AC001,AD001,AE001,SA001,AE002,SD001', '(?<!AE)d3,0,1', 'X','g'
 ),'..X','','g'
 ),',$','','g'
 )

See Demo here

share|improve this answer

answered Mar 28 at 16:59

vmarolivmaroli

54088 silver badges1616 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you much vmaroli your solution really worked. I didn't understand the use of "0,1 in the code. Could you please explain?
  
  – Issaq
  Mar 28 at 17:25
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  ,0,1 is for matching zero or one coma character.
  
  – vmaroli
  Mar 29 at 0:57
  
  

  
  
  
  

add a comment
 | 

Here is one option calling regex_replace multiple times, eliminating the "not required" strings little by little in each iteration to arrive at the required output.

SELECT regexp_replace(
 regexp_replace(
 regexp_replace(
 'AC001,AD001,AE001,SA001,AE002,SD001', '(?<!AE)d3,0,1', 'X','g'
 ),'..X','','g'
 ),',$','','g'
 )

See Demo here

share|improve this answer

answered Mar 28 at 16:59

vmarolivmaroli

54088 silver badges1616 bronze badges

SELECT regexp_replace(
 regexp_replace(
 regexp_replace(
 'AC001,AD001,AE001,SA001,AE002,SD001', '(?<!AE)d3,0,1', 'X','g'
 ),'..X','','g'
 ),',$','','g'
 )

share|improve this answer

answered Mar 28 at 16:59

vmarolivmaroli

54088 silver badges1616 bronze badges

answered Mar 28 at 16:59

vmarolivmaroli

54088 silver badges1616 bronze badges

answered Mar 28 at 16:59

vmarolivmaroli

54088 silver badges1616 bronze badges

0

I would convert the list to an array, unnest that to rows then filter out those that should be kept and aggregate it back to a string:

select string_agg(t, ',')
from unnest(string_to_array('AC001,AD001,AE001,SA001,AE002,SD001',',') as x(t)
where x.t like 'AE%'; --<< only keep those

This is independent of the number of elements in the string and can easily be extended to support more complex conditions.

---

This is a good example why storing comma separated values in a single column is not such a good idea to begin with.

share|improve this answer

answered Mar 28 at 19:30

a_horse_with_no_namea_horse_with_no_name

333k5555 gold badges518518 silver badges610610 bronze badges

add a comment
 | 

0

I would convert the list to an array, unnest that to rows then filter out those that should be kept and aggregate it back to a string:

select string_agg(t, ',')
from unnest(string_to_array('AC001,AD001,AE001,SA001,AE002,SD001',',') as x(t)
where x.t like 'AE%'; --<< only keep those

This is independent of the number of elements in the string and can easily be extended to support more complex conditions.

---

This is a good example why storing comma separated values in a single column is not such a good idea to begin with.

share|improve this answer

answered Mar 28 at 19:30

a_horse_with_no_namea_horse_with_no_name

333k5555 gold badges518518 silver badges610610 bronze badges

add a comment
 | 

I would convert the list to an array, unnest that to rows then filter out those that should be kept and aggregate it back to a string:

select string_agg(t, ',')
from unnest(string_to_array('AC001,AD001,AE001,SA001,AE002,SD001',',') as x(t)
where x.t like 'AE%'; --<< only keep those

This is independent of the number of elements in the string and can easily be extended to support more complex conditions.

---

This is a good example why storing comma separated values in a single column is not such a good idea to begin with.

share|improve this answer

answered Mar 28 at 19:30

a_horse_with_no_namea_horse_with_no_name

333k5555 gold badges518518 silver badges610610 bronze badges

select string_agg(t, ',')
from unnest(string_to_array('AC001,AD001,AE001,SA001,AE002,SD001',',') as x(t)
where x.t like 'AE%'; --<< only keep those

share|improve this answer

answered Mar 28 at 19:30

a_horse_with_no_namea_horse_with_no_name

333k5555 gold badges518518 silver badges610610 bronze badges

answered Mar 28 at 19:30

a_horse_with_no_namea_horse_with_no_name

333k5555 gold badges518518 silver badges610610 bronze badges

answered Mar 28 at 19:30

a_horse_with_no_namea_horse_with_no_name

333k5555 gold badges518518 silver badges610610 bronze badges