sql - Reformatting data in column -

-

have kinda weird here. have database that's called fldoc. in has column called sentence contains 7 numbers represent length of time. 

example: 0050000 0750000 0000600 0040615 0000110

in 7 digits length of type since digits represent yyymmdd

so i'd script can convert this:

5y 00m 00d 75y 00m 00d 6m (or 000y 6m 00d fine well) 4y 6m 15d  etc etc

thanks in advance...

concat new sql server 2012. if have previous version of sql server, instead achieve desired output:

select sentence     ,(         case              when cast(left(sentence, 3) int) > 0                 cast(cast(left(sentence, 3) int) varchar(3)) + 'y '             else cast(left(sentence, 3) varchar(3)) + 'y '             end +          case              when cast(substring(sentence, 4, 2) int) > 0                 cast(cast(substring(sentence, 4, 2) int) varchar(2)) + 'm '             else cast(substring(sentence, 4, 2) varchar(2)) + 'm '             end +           case              when cast(right(sentence, 2) int) > 0                 cast(cast(right(sentence, 2) int) varchar(3)) + 'd'             else cast(right(sentence, 2) varchar(3)) + 'd'             end         ) new_sentence fldoc;

sql fiddle demo

update

to answer question below in comments, maybe write update statement this:

update fldoc  set sentence = (         case              when cast(left(sentence, 3) int) > 0                 cast(cast(left(sentence, 3) int) varchar(3)) + 'y '             else cast(left(sentence, 3) varchar(3)) + 'y '             end +          case              when cast(substring(sentence, 4, 2) int) > 0                 cast(cast(substring(sentence, 4, 2) int) varchar(2)) + 'm '             else cast(substring(sentence, 4, 2) varchar(2)) + 'm '             end +           case              when cast(right(sentence, 2) int) > 0                 cast(cast(right(sentence, 2) int) varchar(3)) + 'd'             else cast(right(sentence, 2) varchar(3)) + 'd'             end         )

Comments

Post a Comment

Popular posts from this blog

Android : Making Listview full screen -

-
<linearlayout xmlns:android="http://schemas.android.com/apk/res/android" xmlns:tools="http://schemas.android.com/tools" android:layout_width="match_parent" android:layout_height="match_parent" android:orientation="vertical" tools:context=".mainactivity"> <edittext android:layout_margintop="5dp" android:layout_width="match_parent" android:layout_height="50dp" android:background="@drawable/location_edittext" android:id="@+id/locationeditext" android:textsize="15sp" android:textcolor="#999" android:paddingleft="15dp" /> <com.example.ravi_gupta.slider.viewpagercustomduration android:layout_width="wrap_content" android:layout_height="0dp" android:id="@+id/viewpager" android:scrollbarstyle="outsideoverlay" android:layout_weight="0.5...
Read more

javascript - Parse JSON from the body of the POST -

-
i'm using cameratag.com record video on website. service can send json response server can save data database. don't know how proceed here: the documentation states please note json repsonse passed in body of post , not form parameters. here example of how parse response in ruby: data = json.parse(request.raw_post) video_uuid = data["uuid"] but don't know how data on javascript. i'm not experienced in js please bare me :) post sample: { "uuid": "00012710-4b65-0131-ffe6-22000a499ea4", "camera_uuid": "98373a20-79ee-0130-3e42-1231390fcc11", "created_at": "2013-12-20t05:25:02.000z", "percent_complete": 100, "short_code": "kktb4q", "metadata": { }, "state": "published", "recorded_from": "http://singwho.com", "publish_type": null, "formats": [ { "name...
Read more

javascript - How to Hide Date Menu from Datepicker in yii2 -

-
basically in datetimepicker widget date choose first time, how can hide date option widget , filter according time. tried not working want hide remove button [ 'attribute'=>'time', 'label'=>'time', 'format'=>'raw', 'value'=>function($data){ return date('h:i:s',strtotime($data->time)); }, 'filter'=> datetimepicker::widget([ 'model' => $searchmodel, 'attribute' => 'time', 'clientoptions' => [ 'autoclose' => true, 'format' => 'hh:ii', 'showon' =...
Read more