Dataphor SQL RAC (Relational Application Companion)


A site of hope for those looking for a true relational database system

Thursday, June 07, 2007

Dataphor - trimming digits and letters from a string

Trimming leading zeros and trailing letters from a string.

This question was raised in the thread:
microsoft.public.sqlserver.programming
Tuesday, June 05, 2007 2:08 PM
Couple of update statements
http://tinyurl.com/2n73xf

This can done quite easily in Dataphors D4 language without the
need of combining string functions into complicated expressions.

The following D4 constructs can be used to return a string
meeting the criteria:

Indexer Expression
http://www.alphora.com/docs/O-System.iIndexer.html

IndexOfAny
http://www.alphora.com/docs/O-System.IndexOfAny.html

Concat (Concatenate)
http://www.alphora.com/docs/O-System.Concat.html

For updating using a table variable or view see:

Join/Outer Join/Lookup
http://www.alphora.com/docs/DDGRepresentingDatawithTablesandViews-DerivedTableVariables-Join.html
http://www.alphora.com/docs/D4LGTableExpressions-OuterJoin.html

Here is some sample data entered into MS Sql Server 2005 that
we will access with Dataphor:

create table LTLData (RowID int primary key,ProNum varchar(25) )
insert into LTLData  Values (1,'00234')
insert into LTLData  Values (2,'030234BD')
insert into LTLData  Values (3,'33030234BD')
insert into LTLData  Values (4,'0233030234BD')
insert into LTLData  Values (5,'0000234BD')
insert into LTLData  Values (6,'233030234')
insert into LTLData  Values (7,'00000ABD')
insert into LTLData  Values (8,'BDBDBD')

So for example given the string ''030234BD' we want to trim the
leading '0' and the trailing 'BD' and return '30234'.

An easy way to do this is transpose the string into rows of a
single character and work with the rows to omit the leading
zeros and trailing letters. Once the offending rows (characters)
are omitted then concatenate the rows to form the new string.

Here we use a table of numbers (numbers with a single column num)
and the indexer to split each string into rows of a single character.
A list of digits of type string (NumList1) is used with IndexOfAny
to add the first occurrance of any non zero digit in the string to
each row (FirstDigit). If a string has no digit from 1-9 then IndexOfAny
returns -1. The 'with {IgnoreUnsupported = 'true'}' is used to suppress
the message Dataphor returns that says there is no support in Sql Server
for the IndexOfAny function.

var NumList1:list(String):={'1','2','3','4','5','6','7','8','9'};
select
(
 numbers //table of digits (num) from 0-100.
   times //Like an sql cross join
         //Get the count (Cnt) of the items in the string and location of 1st digit.
     LTLData add{Length(ProNum.Trim()) Cnt,ProNum.IndexOfAny(NumList1) FirstDigit}
       with {IgnoreUnsupported = 'true'}
        where num<=Cnt-1
          {RowID,ProNum,num Index,FirstDigit,ProNum[num] Char}
 )
  order by {RowID,Index};

Here is a sample of the result:

RowID ProNum       Index FirstDigit Char
----- ------------ ----- ---------- ----
1     00234        0     2          0   
1     00234        1     2          0   
1     00234        2     2          2   
1     00234        3     2          3   
1     00234        4     2          4   
2     030234BD     0     1          0   
2     030234BD     1     1          3   
2     030234BD     2     1          0   
2     030234BD     3     1          2   
2     030234BD     4     1          3   
2     030234BD     5     1          4   
2     030234BD     6     1          B   
2     030234BD     7     1          D   
.

7     00000ABD     0     -1         0   
7     00000ABD     1     -1         0   
7     00000ABD     2     -1         0   
7     00000ABD     3     -1         0   
7     00000ABD     4     -1         0   
7     00000ABD     5     -1         A   
7     00000ABD     6     -1         B   
7     00000ABD     7     -1         D   

We now add a single where statement to omit leading zeros and
trailing letters.

var NumList1:list(String):={'1','2','3','4','5','6','7','8','9'};
select
(
 numbers //table of digits (num) from 0-100.
   times //Like a cross join
       //Get the count (Cnt) of the items in the string and location of 1st digit.
     LTLData add{Length(ProNum.Trim()) Cnt,ProNum.IndexOfAny(NumList1) FirstDigit}
       with {IgnoreUnsupported = 'true'}
        where num<=Cnt-1
          {RowID,ProNum,num Index,FirstDigit,ProNum[num] Char}
 )
       where (Index>=FirstDigit) and (FirstDigit<>-1 ) and (IsDigit(Char))
   order by {RowID,Index};

RowID ProNum       Index FirstDigit Char
----- ------------ ----- ---------- ----
1     00234        2     2          2   
1     00234        3     2          3   
1     00234        4     2          4   
2     030234BD     1     1          3   
2     030234BD     2     1          0   
2     030234BD     3     1          2   
2     030234BD     4     1          3   
2     030234BD     5     1          4   
3     33030234BD   0     0          3   
3     33030234BD   1     0          3   
3     33030234BD   2     0          0   
3     33030234BD   3     0          3   
3     33030234BD   4     0          0   
3     33030234BD   5     0          2   
3     33030234BD   6     0          3   
.
6     233030234    0     0          2   
6     233030234    1     0          3   
6     233030234    2     0          3   
6     233030234    3     0          0   
6     233030234    4     0          3   
6     233030234    5     0          0   
6     233030234    6     0          2   
6     233030234    7     0          3   
6     233030234    8     0          4   

The where statement did not affect RowID 6 but completely eliminated
RowIDs 7 and 8.

The Concat operator can now be used to form a new string based on the
rows meeting the criteria. The Index column is used in Concat to order
the new string in the same way it was transposed. A delimiter (Del) is
defined as an empty string ('') so when the new string is formed there
are is no separation between the characters (Char).

Instead of operating on the rows of LTLData, here is a select statement
that trims a single string. The 'with {IgnoreUnsupported = 'true'}' is
used with Concat since we have not mapped Concat to any Sql Server
function. Like IndexOfAny the Dataphor server will assume responsibility
for this operation. (Interested readers can search Dataphor help for
'query chunking' to see how Dataphor interacts with a device, ie Sql Server.)

var NumList1 := {'1','2','3','4','5','6','7','8','9'};
var Str:='030234BD';
select
Concat({Char,Del}
from
 (
  numbers
   where num<=Length(Str.Trim())-1
    {Str StrNum,num Index,Str[num] Char,Str.IndexOfAny(NumList1) FirstDigit,'' Del}
      with {IgnoreUnsupported = 'true'}
        where (Index>=FirstDigit) and (FirstDigit<>-1 ) and (IsDigit(Char))
  )
    order by {Index}  ) with {IgnoreUnsupported = 'true'};
 
Which returns '30234'.

We can now use the above select to create an operator that will return
a new string for each row of LTLData.

create operator TrimStr(Str:String):String
begin
var NumList1 := {'1','2','3','4','5','6','7','8','9'};
result:= 
Concat({Char,Del}
from
 (
  numbers
   where num<=Length(Str.Trim())-1
    {Str StrNum,num Index,Str[num] Char,Str.IndexOfAny(NumList1) FirstDigit,'' Del}
      with {IgnoreUnsupported = 'true'}
        where (Index>=FirstDigit) and (FirstDigit<>-1 ) and (IsDigit(Char))
  )
    order by {Index}  ) with {IgnoreUnsupported = 'true'};
end;   

We now use operator TrimStr to return the correctly trimmed new string (NewStr).

select
 LTLData add{TrimStr(ProNum) NewStr}
          with {IgnoreUnsupported = 'true'};
         
RowID ProNum       NewStr    
----- ------------ ----------
1     00234        234       
2     030234BD     30234     
3     33030234BD   33030234  
4     0233030234BD 233030234 
5     0000234BD    234       
6     233030234    233030234 
7     00000ABD     <No Value>
8     BDBDBD       <No Value>           

The ProNum column in LTLData can now be updated using TrimStr.
The 'IsNotNil()' is similar to the sql 'not IsNull()'. So only ProNum
values will be updated where TrimStr return a non-nil(null) value.
(Note that D4 uses the term 'nil' whereas sql uses 'null'.) The
"with {ShouldSupport='false'}" tag is used to tell the Dataphor
sever to take responsibility for evaluating 'IsNotNil(TrimStr(ProNum))'.
There is no way for Sql Server to evaluate this expression. A
"{ShouldSupport='true'}" (the default) means Sql Server should
try to support the expression. Without the "with {ShouldSupport='false'}"
the update will execute but Dataphor will issue a warning message.

update LTLData
  set { ProNum:= TrimStr(ProNum) }
    where IsNotNil(TrimStr(ProNum) with {ShouldSupport='false'} )  ;     

select LTLData;  

RowID ProNum   
----- ---------
1     234      
2     30234    
3     33030234 
4     233030234
5     234      
6     233030234
7     00000ABD 
8     BDBDBD 


Finally, we recreate LTLData and show a query which uses Concat in
a group by to form new trimmed strings for each RowID.

var NumList1 := {'1','2','3','4','5','6','7','8','9'};
select
 numbers
   times
    LTLData add{Length(ProNum.Trim()) Cnt,ProNum.IndexOfAny(NumList1) FirstDigit}
        with {IgnoreUnsupported = 'true'}
     where num<=Cnt-1
      {RowID,ProNum,num Index,FirstDigit,ProNum[num] Char,'' Del}
        where (Index>=FirstDigit) and (FirstDigit<>-1 ) and (IsDigit(Char))
            group by {RowID}
              add
                 {Max(ProNum) ProNum, Concat(Char,Del order by {RowID,Index}) NewPro}
                    order by {RowID};

RowID ProNum       NewPro   
----- ------------ ---------
1     00234        234      
2     030234BD     30234    
3     33030234BD   33030234 
4     0233030234BD 233030234
5     0000234BD    234      
6     233030234    233030234


Update with a view.
We can define a view and use it in a join to update ProNum with NewPro (from the view).

create view LTLDataView                    
 numbers
   times
    LTLData add{Length(ProNum.Trim()) Cnt,ProNum.IndexOfAny( {'1','2','3','4','5','6','7','8','9'}) FirstDigit}
        with {IgnoreUnsupported = 'true'}
     where num<=Cnt-1
      {RowID,ProNum,num Index,FirstDigit,ProNum[num] Char,'' Del}
        where (Index>=FirstDigit) and (FirstDigit<>-1 ) and (IsDigit(Char))
            group by {RowID}
              add
                 {Max(ProNum) ProNum, Concat(Char,Del order by {RowID,Index}) NewPro} ;
                
The lookup operator is similar to a join but can be used to
control just which table(s) should be the target of an update.
We only wish to update LTLData and don't want to go thru the
view to do it. We only want the view to supply the new string NewPro.
The lookup encapsulates this idea. Note the lookup (join) is a
natural join using RowID.

update LTLData lookup (LTLDataView remove{ProNum} )
   set { ProNum:= NewPro } ;  

select LTLData;

RowID ProNum   
----- ---------
1     234      
2     30234    
3     33030234 
4     233030234
5     234      
6     233030234
7     00000ABD 
8     BDBDBD   

Instead of a view a table variable could be also be used.

var NumList1 := {'1','2','3','4','5','6','7','8','9'};
var Y:=
 numbers
   times
    LTLData add{Length(ProNum.Trim()) Cnt,ProNum.IndexOfAny(NumList1) FirstDigit}
        with {IgnoreUnsupported = 'true'}
     where num<=Cnt-1
      {RowID,ProNum,num Index,FirstDigit,ProNum[num] Char,'' Del}
        where (Index>=FirstDigit) and (FirstDigit<>-1 ) and (IsDigit(Char))
            group by {RowID}
              add
                 {Max(ProNum) ProNum, Concat(Char,Del order by {RowID,Index}) NewPro} ;

update LTLData lookup (Y remove{ProNum} )
   set { ProNum:= NewPro } ;  

select LTLData;

RowID ProNum   
----- ---------
1     234      
2     30234    
3     33030234 
4     233030234
5     234      
6     233030234
7     00000ABD 
8     BDBDBD   

Bye for now,
steve




37 comments:

Anonymous said...

...please where can I buy a unicorn?

Anonymous said...

Ich tue Abbitte, dass sich eingemischt hat... Ich finde mich dieser Frage zurecht. Geben Sie wir werden besprechen. Schreiben Sie hier oder in PM. viagra kaufen cialis online [url=http//t7-isis.org]cialis billig[/url]

Anonymous said...

Se junto. Y con esto me he encontrado. Discutiremos esta pregunta. [url=http://csalamanca.com/comprar-viagra-generico/ ]viagra 50 mg [/url] Que palabras adecuadas... El pensamiento fenomenal, admirable viagra sin receta

Anonymous said...

Do You interesting of [b]Female use of Viagra[/b]? You can find below...
[size=10]>>>[url=http://listita.info/go.php?sid=1][b]Female use of Viagra[/b][/url]<<<[/size]

[URL=http://imgwebsearch.com/30269/link/viagra%2C%20tramadol%2C%20zithromax%2C%20carisoprodol%2C%20buy%20cialis/1_valentine3.html][IMG]http://imgwebsearch.com/30269/img0/viagra%2C%20tramadol%2C%20zithromax%2C%20carisoprodol%2C%20buy%20cialis/1_valentine3.png[/IMG][/URL]
[URL=http://imgwebsearch.com/30269/link/buy%20viagra/3_headsex1.html][IMG]http://imgwebsearch.com/30269/img0/buy%20viagra/3_headsex1.png[/IMG][/URL]
[b]Bonus Policy[/b]
Order 3 or more products and get free Regular Airmail shipping!
Free Regular Airmail shipping for orders starting with $200.00!

Free insurance (guaranteed reshipment if delivery failed) for orders starting with $300.00!
[b]Description[/b]

Generic Viagra (sildenafil citrate; brand names include: Aphrodil / Edegra / Erasmo / Penegra / Revatio / Supra / Zwagra) is an effective treatment for erectile dysfunction regardless of the cause or duration of the problem or the age of the patient.
Sildenafil Citrate is the active ingredient used to treat erectile dysfunction (impotence) in men. It can help men who have erectile dysfunction get and sustain an erection when they are sexually excited.
Generic Viagra is manufactured in accordance with World Health Organization standards and guidelines (WHO-GMP). Also you can find on our sites.
Generic [url=http://viagra.wilantion.ru]Viagra 100mg pills[/url] is made with thorough reverse engineering for the sildenafil citrate molecule - a totally different process of making sildenafil and its reaction. That is why it takes effect in 15 minutes compared to other drugs which take 30-40 minutes to take effect.
[b]taking cialis and viagra together
viagra purchase online
viagra max dose
Viagra Y Sus Efectos Secundarios
online viagra pills
zillow generic sertraline
Viagra And Cgmp
[/b]
Even in the most sexually liberated and self-satisfied of nations, many people still yearn to burn more, to feel ready for bedding no matter what the clock says and to desire their partner of 23 years as much as they did when their love was brand new.
The market is saturated with books on how to revive a flagging libido or spice up monotonous sex, and sex therapists say “lack of desire” is one of the most common complaints they hear from patients, particularly women.

Anonymous said...

majong

http://majong.socialgo.com

viagra online said...

thanks for collect and post this useful information about A site of hope for those looking for a true relational database system thanks I really appreciate your help

Anonymous said...

To be a adroit lenient being is to have a amiable of openness to the in the seventh heaven, an cleverness to trust aleatory things beyond your own pilot, that can govern you to be shattered in unequivocally exceptional circumstances for which you were not to blame. That says something uncommonly weighty thither the get of the righteous compulsion: that it is based on a trustworthiness in the unpredictable and on a willingness to be exposed; it's based on being more like a weed than like a sparkler, something rather tenuous, but whose mere special attractiveness is inseparable from that fragility.

Anonymous said...

n harry's time, at some dated, our inner fire goes out. It is then burst into enthusiasm beside an be faced with with another human being. We should all be under obligation quest of those people who rekindle the inner inspiration

Anonymous said...

In the whole world's life, at some dated, our inner pep goes out. It is then bust into passion by an face with another benign being. We should all be indebted for the duration of those people who rekindle the inner spirit

Anonymous said...

In everyone's time, at some dated, our inner throw goes out. It is then blow up into flame by an be faced with with another hominoid being. We should all be indebted quest of those people who rekindle the inner transport

Anonymous said...

In everyone's life, at some pass‚, our inner fire goes out. It is then blow up into zeal by an face with another hominoid being. We should all be under obligation for those people who rekindle the inner inclination

Anonymous said...

As your obligation is strengthened you will-power tumble to that there is no longer the need to take a discrimination of repress, that things commitment bubble as they last will and testament, and that you drive flow with them, to your fantabulous delight and benefit.

[url=http://petitelectromenager.eu]Aspirateur Hoover[/url]
Barbecue électrique

Anonymous said...

A untroubled beloved maturity is the reward of a well-spent youth. As a substitute for of its bringing sad and melancholy prospects of rot, it would hand out us hopes of eternal youth in a recovered world.

Anonymous said...

To be a adroit charitable being is to from a philanthropic of openness to the world, an cleverness to trusteeship uncertain things beyond your own restrain, that can take you to be shattered in hugely exceptional circumstances as which you were not to blame. That says something very outstanding about the prerequisite of the ethical compulsion: that it is based on a corporation in the up in the air and on a willingness to be exposed; it's based on being more like a plant than like a treasure, something rather dainty, but whose extremely item beauty is inseparable from that fragility.

Anonymous said...

To be a adroit charitable being is to from a kind of openness to the mankind, an gift to guardianship aleatory things beyond your own restrain, that can front you to be shattered in uncommonly exceptional circumstances for which you were not to blame. That says something uncommonly important with the get of the ethical compulsion: that it is based on a trustworthiness in the up in the air and on a willingness to be exposed; it's based on being more like a spy than like a prize, something somewhat tenuous, but whose extremely item handsomeness is inseparable from that fragility.

Anonymous said...

I'm really Glad i found this web site.Added beyondsql.blogspot.com to my bookmark!

Anonymous said...

how to unlock iphone 4
unlock iphone 4
unlock iphone 4

http://www.gameculture.com/2011/03/15/fun-pic-day-resident-evil http://matadortravel.com/forum/europe/student-apartment-paris
I have an HP Pavilion dv6000 with Windows Vista. My mouse locks up and the computer freezes. I thought it was a virus but after running a scan, it still kept happening. I tried cleaning up my registry but I don't think that helped. HP was no help to me so what should I do?
iphone 4 unlock iphone 4 unlock

iphone 4 unlock [url=http://unlockiphone44.com]how to unlock iphone 4[/url] how to unlock iphone 4 unlock iphone 4

Anonymous said...

the magic school bus ebook http://audiobookscollection.co.uk/it/J-rgen-Fuchs/m68665/ netdotcom pdf ebook [url=http://audiobookscollection.co.uk/fr/Populaire-Livres-Audio/l10/?page=2]a world undone ebook[/url] ebook erma bombeck

Anonymous said...

The other day, while I was at work, my cousin stole my iphone and tested to see
if it can survive a twenty five foot drop, just so she can be
a youtube sensation. My iPad is now broken and she has 83 views.

I know this is completely off topic but I had to share it with someone!


Feel free to visit my web-site ... trade marking a name

Anonymous said...

I will immediately seize your rss as I can't to find your e-mail subscription hyperlink or e-newsletter service. Do you've any?
Kindly allow me realize so that I may subscribe.
Thanks.

Here is my web-site :: 411 pain Florida truth network

Anonymous said...

design basics ebook david lauer http://audiobooksworld.co.uk/es/Winter-Holiday/p10411/ power system analysis grainger ebook [url=http://audiobooksworld.co.uk/Drug-Education-Library-Hallucinogens/p150024/]download ebook ebook[/url] free ebook down chaos fractal
[url=http://audiobooksworld.co.uk/Margaret-Weis/m81730/][img]http://audiobooksworld.co.uk/image/6.gif[/img][/url]

Anonymous said...

I have to thank you for the efforts you've put in penning this site. I'm hoping to view the same high-grade
blog posts by you later on as well. In fact,
your creative writing abilities has motivated me to get my own blog now ;)

Here is my weblog :: facebook quotes
my webpage: life quotes

Anonymous said...

Thank you for the good writeup. It actually was once a enjoyment account it.

Glance complex to more brought agreeable from you!
By the way, how could we communicate?

Here is my web site: Kosher Catering Los Angeles

Anonymous said...

dating latin women websites [url=http://freeinternetdating.info/romance/free-lesbian-romance-ebook]free lesbian romance ebook[/url] dork dating site
christian cafe dating site http://freeinternetdating.info/relationships/trusting-in-relationships scott peterson dating
kitten sim dating 3 cheats [url=http://freeinternetdating.info/meet/meet-and-fuck-sex-games]daneel harris who dating[/url] leeds dating

Anonymous said...

I've been surfing on-line greater than 3 hours lately, yet I never found any interesting article like yours. It's beautiful price sufficient for me.
Personally, if all site owners and bloggers made good content material as you probably did, the internet shall be
a lot more helpful than ever before.

Have a look at my web-site: http://dabagirls.com?wptMostViewed

Anonymous said...

I like the valuable info you provide in your articles.
I'll bookmark your blog and check again here regularly. I'm
quite sure I will learn many new stuff right here!
Good luck for the next!

My web blog :: Participate in Student Party and Have Fuck

Anonymous said...

parental control mtv dating nico [url=http://freeinternetdating.info/personals/swingers-personals-ridgeview-south-dakota]swingers personals ridgeview south dakota[/url] gay black dating denver
2008 online dating site http://freeinternetdating.info/love/summer-love-by-justin-m3 mre dating
dating fossils [url=http://freeinternetdating.info/meet/meet-and-fuck-leila-game]amber smith dating[/url] hookup dating site

Anonymous said...

It's a shame you don't have a donate button! I'd definitely donate to this outstanding blog! I suppose for now i'll settle for book-marking and adding your RSS feed to
my Google account. I look forward to brand new updates and will share this blog with my Facebook group.
Chat soon!

Feel free to surf to my webpage ... sex voyeur

Anonymous said...

I do not know if it's just me or if everybody else encountering issues with your blog. It appears like some of the text within your posts are running off the screen. Can someone else please provide feedback and let me know if this is happening to them too? This could be a problem with my browser because I've had this
happen previously. Thank you

Visit my page: webcam sex

Anonymous said...

What's up, yup this paragraph is truly fastidious and I have learned lot of things from it on the topic of blogging. thanks.

Here is my web page http://www.babesflick.com/video/11785/dawn-ivi-great-soapy-massage.html

Anonymous said...

This paragraph provides clear idea in support of the new viewers of blogging, that truly
how to do running a blog.

Also visit my website: www.jnmassage.info

Anonymous said...

Hey There. I found your blog using msn. This is a really well written article.
I'll be sure to bookmark it and return to read more of your useful info. Thanks for the post. I will definitely comeback.

Feel free to surf to my page handjob-teens.com

Anonymous said...

Good post. I learn something new and challenging on sites I stumbleupon on a daily basis.
It's always useful to read through content from other authors and practice something from their sites.

Look into my web page - www.cuteteenporn.net

Anonymous said...

Wonderful article! That is the type of information that are meant to
be shared around the internet. Disgrace on Google for now not positioning this
submit higher! Come on over and discuss with my web site .
Thanks =)

My web page: www.xxxmoviegalls.com

Anonymous said...

Hi theгe, i read your blog occasionally and i οwn a similar one and
i was just curious if you get a lot of ѕpam feeԁback?
ӏf so hоw do you stop іt, anу plugіn or
anything yοu cаn гeсommend? I get sο much lately it's driving me mad so any assistance is very much appreciated.

Feel free to visit my web-site; reputation management service

Anonymous said...

Hello just wanted to give you a quick heads up.

The text in your article seem to be running
off the screen in Internet explorer. I'm not sure if this is a formatting issue or something to do with web browser compatibility but I figured I'd post to let
you know. The layout look great though! Hope
you get the issue resolved soon. Cheers

Also visit my website ... http://www.analteenexam.
org [http://www.nakedsex.tv/blog/33]

Anonymous said...

Amazing issues here. I am very satisfied to peer your post.
Thank you so much and I'm looking forward to contact you. Will you please drop me a mail?

my page - Petite teens

About Me

My photo
Phoenix, Arizona, United States