sql server - Why is my Derived Column Expression Not Giving Me the Results I Expect? -

-

i extracting data excel sheet , transforming , loading sql server 2012. trying perform derived column transformation on column 'registrationnumber'.

the 'registrationnumber' in following formats , not fixed length:

###/#### ###/#### ###/#### ###/#### ###/#### ###/#### x###/#### x###/#### x###/#### x###/#### x###/#### x###/#### x###/#### x###/#### x###/#### x###/#### x###/#### x###/#### x###/#### x###/#### x###/#### x###/ab xyz##/### xyz##/### abs###/#### abcsedg###fg/####

i need take left of '/' , put in column on sql server , have take right of '/' , put in different column in sql server:

the following expressions in derived column transformation:

derived column: registrationnumber derived column:  replace 'registrationnumber' expression: left([registationnumber],findstring([registrationnumber],"/",1) - 1) data type: unicode string [dt_wstr] length:  255  derived column: copy of registrationnumber derived column:  replace 'copy of registrationnumber' expression: right([copy of registrationnumber],findstring([copy of   registrationnumber],"/",1) - 1) data type: unicode string [dt_wstr] length:  255

for of results did wanted. of them getting results following:

/#### ##/aa #/#### a###xy/####

the right expression should have results this:

####

note: left expression produced valid results

the following task flow:

excel source

to copy column derived column ole db command

left "new" 2012 release of ssis i'm providing formulas use substring people still on 2005/2008

  • slashposition = findstring(registrationnumber,"/",1)
  • left = left(registrationnumber,(slashposition - 1))
  • subleft = substring(registrationnumber,1,(slashposition - 1))
  • right = right(registrationnumber,len(registrationnumber) - (slashposition))
  • subright = substring(registrationnumber,(slashposition + 1),len(registrationnumber) - slashposition)

enter image description here

at core of it, right call slicing off last n character length passing position slash found. instead, position length of string, less slash position

enter image description here

as excel vs cozyroc vs else. that'll opinion based have found have best success when can data in flat file format (delimited or fixed width). assuming that's not option, prefer write queries against excel use native table thing.


Comments

Post a Comment

Popular posts from this blog

php - render data via PDO::FETCH_FUNC vs loop -

-
i've been using 2 methods render data. the first one: function name($id,$name){ return '<div id="'.$id.'">'.$name.'</div>'; } echo implode($pdo->query("select id,name user")->fetchall(pdo::fetch_func,'name')); the second one: $users = $pdo->query("select id,name user")->fetchall(pdo::fetch_obj); foreach($users $user){ echo name($user->id,$user->name); } i don't understand how pdo::fetch_func works. tried figure out. however, not well-documented. could please explain fetch mode? , also, 1 performs better? thank you. both methods wrong , have learn how use templates , how separate business logic presentation logic. $users = $pdo->query("select id,name user")->fetchall(pdo::fetch_obj); tpl::assign('users', $users); is code business logic part. then in template <?php foreach $users $row): ?> <div id="<?=$...
Read more

c++ - OpenCV Error: Assertion failed <scn == 3 ::scn == 4> in unknown function, -

-
i read alot other solution still confused should mine... #include <opencv2/objdetect/objdetect.hpp> #include <opencv2/highgui/highgui.hpp> #include <opencv2/imgproc/imgproc.hpp> #include <iostream> #include <stdio.h> using namespace std; using namespace cv; int main(int argc, const char** argv) { //create cascade classifier object used face detection cascadeclassifier face_cascade; //use haarcascade_frontalface_alt.xml library face_cascade.load("haarcascade_frontalface_alt.xml"); //setup video capture device , link first capture device videocapture capturedevice; capturedevice.open(0); //setup image files used in capture process mat captureframe; mat grayscaleframe; //create window present results namedwindow("outputcapture", 1); //create loop capture , find faces while (true) { //capture new image frame capturedevice >> captureframe; ...
Read more

The canvas has been tainted by cross-origin data in chrome only -

-
uncaught securityerror: failed execute 'getimagedata' on 'canvasrenderingcontext2d': canvas has been tainted cross-origin data i getting above error in chrome. code(following) works fine in mozilla. have not corss domain issue. why above error in chrome? var wheel_canvas, wheel_context, can_w, can_h, wheel_grd; var large_radius = 160; var small_radius = 120; var wheel_canvas = document.getelementbyid('wheel_canvas'); var wheel_context = wheel_canvas.getcontext('2d'); var can_w = $(wheel_canvas).width(); var can_h = $(wheel_canvas).height(); var centerx = can_w / 2, centery = can_h / 2; var wheel_grd = null; test('#ff0000'); function test(hex) { $('#clrpicker').css({'left': 210, 'top': 210 }); inverted = hexbw(hex); $('#color_val_show').val(hex); current_color_hex_val_selected = hex; $('#color_val_show').css({'color':inverted,'background':hex}); fillgradient...
Read more