Extract Numeric and Special Characters Separately

Question

I have column in a table with values stored as

Cost

499.00
£ 7.75
£ 7.75
250.00
£ 5.99
$ 6.05

Now, I need to store these values to another table like

Currency     Cost
RS           499.00
£            7.75
£            7.75 
RS           250.00 
£            5.99 
$            6.05

Plz let me the Query how to do this....

Alex Aza · Answer 1 · 2011-06-02T01:26:29.613

4

insert DestinationTable(Cost, Currency)
select
    case when delimiterIndex > 0 then left(Cost, delimiterIndex) else 'RS' end as Currency,
    right(Cost, len(Cost) - delimiterIndex) as Cost
from
(
    select charindex(' ', Cost) delimiterIndex, *
    from SourceTable
) tt

edited Jun 02 '11 at 01:26

answered Jun 02 '11 at 01:14

Alex Aza

76,499
26
155
134

You probably want to add a case to convert no currrency to 'RS' – Conrad Frix Jun 02 '11 at 01:16
I have been following your answers for the past one week. Really helpful. Love to learn lot more from you. – Sandeep Jun 02 '11 at 02:29

score 0 · Answer 2 · answered Jun 02 '11 at 01:21

I'd use Perl for this...

#!/usr/bin/perl
use strict;
use warnings;

open(FH,"<data.txt");

my @lines=<FH>;

print "Currency"."\t"."Cost\n";

foreach my $line (@lines){
    $line=~s/\n//g;
    if($line ne ""){
        my @split1=split(/\s+/,$line);
        if($split1[0]=~m/[0-9.]/){
            print "\t".$split1[0]."\n";
        }else{
            print $split1[0]."\t".$split1[1]."\n";
        }
    }
}

score 0 · Answer 3 · answered Jun 02 '11 at 01:23

0

You can use SQL-Server string functions:

# Currency
SELECT SUBSTRING(<value>, 1, CHARINDEX(' ', <value>))

# Amount
SELECT SUBSTRING(<value>, CHARINDEX(' ', <value>), LEN(<value>))

answered Jun 02 '11 at 01:23

Eder

1,874
17
34

score 0 · Answer 4 · answered Jun 02 '11 at 01:27

0

You could create a regex CLR assembly like this one and specify in your query what to match on.

Probably a bit more work than the other suggestions but you could re-use the assembly elsewhere should you need it.

answered Jun 02 '11 at 01:27

Fellmeister

591
3
24

score 0 · Answer 5 · answered Jun 02 '11 at 04:33

SELECT
  Currency = COALESCE(NULLIF(LEFT(Cost, CostStart - 1), ''), 'RS'),
  Cost = SUBSTRING(Cost, CostStart, LEN(Cost) - CostStart + 1)
FROM (
  SELECT
    Cost,
    CostStart = PATINDEX('%[0-9]%', Cost)
  FROM atable
) s

This will work even when there's no space between the currency symbol and the sum.

score -1 · Answer 6 · answered Mar 23 '20 at 11:23

StringBuffer alpha = new StringBuffer(), num = new StringBuffer(), special = new StringBuffer();

                    final String txt= txtview2.getText().toString();
                    alpha1.setText(txtview2.getText().toString());
                    num1.setText(txtview2.getText().toString());
                    special1.setText(txtview2.getText().toString());


                    Toast.makeText(MainActivity.this,"you enter string:"+txt,Toast.LENGTH_SHORT).show();
                    for (int i=0; i<txt.length(); i++)
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   {
                        if (Character.isDigit(txt.charAt(i)))
                            num.append(txt.charAt(i));
                        else if(Character.isLetter(txt.charAt(i)))
                            alpha.append(txt.charAt(i));
                        else
                            special.append(txt.charAt(i));
                    }

                    alpha1.setText("The Character: " +alpha);
                    num1.setText("The digit:" + num);
                    special1.setText("The special symbol: " + special);
                }

Extract Numeric and Special Characters Separately

6 Answers6