I have a task to roll certain numbers up what have an ID with a letter at the end.
something like this:
- 194A
194B
- 50A
50D
I need to ‘roll’ values (ie: Sales Amount, Quantities) regardless of the version (A..B..C..)
Here is some sample code that will help create some sample data:
create a fake table called blah:
if object_id('blah') is not null drop table blah
if object_id('blah') is null create table blah (id int primary key identity, mixed_charachters varchar(20))
Now, insert values into that table:
insert into blah (mixed_charachters)
select mixed_charachters
from (select '8B' as mixed_charachters union
select '244' union
select '51B' union
select '172' union
select '247A' union
select '274' union
select '249' union
select '273' union
select '194B' union
select '198' union
select '271' union
select '50A' union
select '276C' union
select '254' union
select '115' union
select '93' union
select '158' union
select '210C' union
select '50D' union
select '102' union
select '265' union
select '250' union
select '51A' union
select '196' union
select '188' union
select '216E' union
select '34' union
select '254B' union
select '276' union
select '65' union
select '78' union
select '178' union
select 'TEXTVALUE union
select '221A' union
select '209' union
select '96A' union
select '73' union
select '190' union
select '262' union
select '258' union
select '278' union
select '194A' union
select '46' union
select '227A') as charachters_to_insert
Now, I can start working with the numeric values that have A LETTER IN IT
select * from blah where isnumeric(mixed_charachters)<>1
