Posted By

bvwatson on 05/28/15


Tagged

regex perl substitution


Versions (?)

RegEx string substitution


 / Published in: Perl
 

URL: http://database.ittoolbox.com/groups/technical-functional/sql-l/sql-query-to-remove-texts-and-numbers-5738855

[The original problem was stated as a SQL (SQL Server 2008) problem. This Perl script demonstrates the desired outcome for the problem statement.]

PROBLEM STATEMENT:

Please help me with SQL Query and requirement is as below:

Below is the Data and I have to remove all texts associated with numbers. If incase in between LETTERS if we see number or numbers, those also should be removed, for ex:A2D104380, here A2D should be removed, others only letters.

Field_Name

AS76573

AEDFG103020

A2D104380

S103282

To add to your point.

Yes you are correct but gain if exists number and again followed by any letters then number also should be removed.

For ex: ASD6D6383884

Here ASD6D should be removed. GH63737. Here only GH should be removed

SOLUTION RESULTS:

Word 1 = AS76573 -> 76573

Word 2 = AEDFG103020 -> 103020

Word 3 = A2D104380 -> 104380

Word 4 = S103282 -> 103282

Word 5 = ASD6D6383884 -> 6383884

Word 6 = GH63737 -> 63737

Word 7 = AS76573AEDFG103020A2D104380S103282ASD6D6383884GH63737 -> 76573103020104380103282638388463737

  1. #!/usr/bin/perl
  2.  
  3. @words = qw (AS76573
  4. AEDFG103020
  5. A2D104380
  6. S103282
  7. ASD6D6383884
  8. GH63737
  9. AS76573AEDFG103020A2D104380S103282ASD6D6383884GH63737
  10. );
  11.  
  12. # RETRIEVE DATA AND APPLY MODS
  13. $pkey = 0;
  14. foreach $orig_word(@words){
  15. $pkey++;
  16. $new_word = $orig_word;
  17. $new_word =~ s/([a-zA-z][0-9][a-zA-Z]|[^\d])//g;
  18. print "\nWord $pkey = $orig_word -> $new_word\n";
  19. }
  20. # done --

Report this snippet  

You need to login to post a comment.