SQL Server: use a cursor to fetch rows, then split a comma separated string in a column, and loop all the values

Some time ago I wrote a post about "How to split a comma separated string and loop it's values in SQL Server".
Today an anonymous user asked how does this apply if you are reading comma separated string from a column in a DB. 

The simplest way would probably be to do these kind of thing in a proper programming language (C++, C#, VB, Java.... you name it)
But if you really need to do this in T-SQL you can simply use a SQL Server cursor to enumerate the rows of the table, and then split the string one-by-one.

So, suppose we have a column in a table that contain comma separated values, we ca use a stored procedure with the following code. (as of today this code is UNTESTED, just because I don't have a SQL Server instance installed on my home computer :-) )

DECLARE @valueList varchar(8000) 

DECLARE @pos INT
DECLARE @len INT
DECLARE @value varchar(8000) 

DECLARE my_string_table_cursor CURSOR
    FOR SELECT my_column_string FROM my_table

OPEN my_string_table_cursor 


    FETCH NEXT FROM my_string_table_cursor
    INTO @valueList 

    WHILE @@FETCH_STATUS = 0
    BEGIN

        set @pos = 0
        set @len = 0

        WHILE CHARINDEX(',', @valueList, @pos+1)>0
        BEGIN
            set @len = CHARINDEX(',', @valueList, @pos+1) - @pos
            set @value = SUBSTRING(@valueList, @pos, @len)
            --SELECT @pos, @len, @value /*this is here for debugging*/
                
            PRINT @value
            --Here is you value
            --DO YOUR STUFF HERE
            --DO YOUR STUFF HERE
            --DO YOUR STUFF HERE
            --DO YOUR STUFF HERE
            --DO YOUR STUFF HERE

            set @pos = CHARINDEX(',', @valueList, @pos+@len) +1
        END 

        FETCH NEXT FROM my_string_table_cursor 
        INTO @valueList  

    END

CLOSE my_string_table_cursor
DEALLOCATE my_string_table_cursor

The table should contain a column with values like
    row 1:  aa,bb,cc,dwefwef,43truygtfuye,w,
    row 2:  trd , ygu , umhb , bhu,
    row 3:  a,b,c,d,e,f,g,h,i,
    row 4:  123,a,456,bbb,kkk,00000,
And the string in each row must end with a comma ","

SQL Code colored with this syntax highlighter

Plantronics M165 bluetooth headset teardown - Making a hole for the microphone to fix the low volume and make the other person hear you better

Some month ago I decided I should get a Bluetooth headset for my smartphone, mainly to get less RF to my head :-)
After some research and reviews reading activity I opted for a Plantronics M165 Marque 2 (I bought it on Amazon, they always have a fantastic customer service).
It looked nice, with a practical "hear plugging" system, and the reviews were good.

Effectively the headset is very nice, very light, and fit perfectly in my hears.

BUT, there is an issue, a big one (in my opinion).
The sensitivity of the mike is too low, if there is a bit of noise around me, the person I'm talking to hear the noise instead of my voice.
So, this Plantronics M165 Mk2 works nicely when I'm in a silent room, but if I'm outside near a road it is totally useless, I need to turn it off and recall the person without the headset. This happened to me so many times that I've stopped using this headset.

Just to be clear about the level of noise I'm talking about: when I'm in the office, in the outskirts of the city, then I go on the balcony: we are on the 4th floor, and the nearest road is at about 60mt. Here, the noise of the road is enough that I can't use the headset.

I asked myself why I can't use the headset with such a low noise... maybe that my headset is broken/defective? it is possible...
But I think there is a better explanation: this headset DOESN'T have a hole for the microphone.

Take a look at the following image, a photo of the frontal part of the headset.
There isn't any hole, it's all solid plastic, and the microphone is behind this plastic.
How can it hear my voice?

There is second mike (for noise reduction) that instead it's near some hole facing "the noise" on the opposite side of my mouth.
Here you can see an image of the hole near the "noise reduction" mike.
I've circled them in red because they are so tiny that even with a macro photo you can barely see them. They are very tiny, about 2-3mm x 0,5mm and are "trapped" between the plastic bezel of the headset.

In my opinion, this can't work well.
Any headset I've ever seen have a hole for the mike, why the Plantronics M165 doesn't have it?

Now, I think I understood the problem: no hole for the mike.
I have also decided that this headset is useless for me, because it can work only in a silent room, which is not the the typical situation where I want to use it.

So maybe I can find a way to fix it? If only I knew where exactly the mike is, then I could make a hole for it.
So, why not try to dismantle it? :-)

I always loved to dismantle things, and I know that try to dismantle a headset can be a one-way street. Sometime they are glued together, and they are so tiny that it's really easy to break something.
Anyway, I should at least try :-)

So here is my venture in dismantling and fixing my headset.
To dismantle it I've used some plastic opening tool and some mini screwdriver.

Typical plastic opening tool

After trying to put the opening tool in any spot I could find, I've been able to open my way into the headset.
In the following image you can see the partly opened headset.
In red I've circled the only existing opening on the headset, them are near the "noise reduction" mike. Through these holes the headset can listen for the ambient noise.
Circled in yellow there is one of the 5 clips that hold the headset together.

  Here are some other images of the partly dismantled headset.
 

After some more pulling and bending I've been able to open the headset.

In the following images you can see the external part of the headset.
Circled in Green there are the 5 clips that keep the headset together.
Circled in Red and Orange there are other 2 clips that hold the headset together.
These last 2 clips are very hard to "unclip" because them are very tiny, and you cant use the typical plastic opening tool to unclip them (because them are placed in the corer of the headset)

As you can see from the photo, the Orange one is missing, I  broke it during the opening procedure... :-(

 

Now we can take a look inside the headset.
Circled in red there are the hooks of the clips.
Circled in green the leds of the headset.
Circled in pink the switch of the main button.
Circled in yellow is the bluetooth antenna.
Circled in blue is what I suppose to be the "noise reduction" mike.

And where is the microphone for the voice?
Let's take a closer look...

 Look at the object circled in red, it looks very similar the the noise reduction mike (circled in blu)

Let me check better.. I can also slip a piece of paper under it, it definitely seem to be the voice mike.

Now I know exactly where I need to make a hole, on the external plastic of the headset :-)
So, let's drill a hole in this deaf plastic shell!
To make the hole I've simply used my old trusty Olfa Cutter

 

And after some hand drilling... Ta-daaaa!
Here is my modded version of the Plantronics M165 Marque 2, now with a hole for the mike, so that the person you are calling can hear you better :-)

Here you can see the voice-microphone through the hole :-)

I reassembled the headset, and I'm happy to tell you that it stay together perfectly, even with a broken clip.

Clearly the headset have now some sign of the surgery procedure it went over :-)

 

 

Now the headset works way better than before, and I can talk on the balcony of the office without any issue! :-)
 

Single click conversion form any format to EPUB using Calibre v2.0 - now with batch multiple files conversion, and rename instead than overwrite

One year ago I developed a simple script to convert any document (PDF, RTF, TXT,...) to EPUB format, using Calibre.
The script was very simple to use, just put the script in the Calibre folder, and then drag and drop the file you want to convert on the script.

Today a reader asked for 2 new features:

• being able to convert multiple files at once
• don't overwrite the epub file that may already exist

So I implemented these features in an updated version of the script.

Click here to Download the new version of the script.

You need to place this script in the same folder where you have the Calibre executable.

Then you can drag and drop the file you want to convert on the script:

After the conversion, you'll find the new converted EPUB file in the same folder as the original file.

Check the original article for more information about this script, and how to use it.

About the new features
Now, for example, you can select 10 PDF files and drop them on the script, and they'll get converted to EPUB (and Author and Title will be set, check the original article for more information)

If the epub file already exist, now the script will add current date/time to the filename.
Example: you try to convert a file named "Asimov - Nightfall.pdf", the script try to create a file named "Asimov - Nightfall.epub", but if this file already exist the script will then create a file named "Asimov - Nightfall 01_01_2014 21.13.52,34.epub" by adding current date/time to the filename.

This updated script, in theory, can convert up to 400 files at once.

The real limitation came from Windows max command line length, that is limited to 8191characters.
So, in real life, you probably won't be able to drag and drop 400 files on the script, because their full pathname will be longer than 8191 characters.
By "Full pathname" i mean the full path + filename of the files, so a full pathname of a file my look something like "E:\eBook\asimov books\series\Asimov - Empire 1.pdf" whick is long 52 chars.
So if you have files like that, you can convert a maximum of about 8192 / 52 = 157 files at once.
Depending on you filename and folder structure your mileage may vary.

Inside the script...
Here is a color-coded version of the EPUB conversion script, the same script you can download here.
(color coded version of the script created by the courtesy of http://hilite.me/ using the 'native' profile, and then tweaking color by hand)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457

@echo off setlocal EnableDelayedExpansion rem find script path set scriptPath=%~dp0 set scriptPath=%scriptPath:~0,-1% rem "%scriptPath%\pyprogram.exe" /myparam=123_abcd IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") SHIFT IF EXIST "%~1" (CALL :epubConvert "%~1") rem this goto if here to skip the code of the confesion function goto :endOfBatchfile rem start of a sub function :epubConvert set filename=%~n1 REM split authors from title using the first "-" as separator REM using ! and EnableDelayedExpansion instead of % so that filename containing parenthesis wont cause issue FOR /F "usebackq tokens=1* delims=-" %%a in ('!filename!') do ( set autore= %%a set titolo= %%b ) rem trim authors name extra space from left/right for /f "tokens=* delims= " %%a in ("!autore!") do set autore=%%a set autore=%autore%## set autore=%autore: ##=##% set autore=%autore: ##=##% set autore=%autore: ##=##% set autore=%autore: ##=##% set autore=%autore: ##=##% set autore=%autore:##=% echo. Authors: "%autore%" rem trim titles extra space from left/right for /f "tokens=* delims= " %%a in ("!titolo!") do set titolo=%%a set titolo=%titolo%## set titolo=%titolo: ##=##% set titolo=%titolo: ##=##% set titolo=%titolo: ##=##% set titolo=%titolo: ##=##% set titolo=%titolo: ##=##% set titolo=%titolo:##=% echo. Title: "%titolo%" echo. echo. REM if the filename doesent contains any "-" then %titolo% will be empty IF "%titolo%"=="" ( set titolo=!autore! set autore=unknown ) set InputfileParameters=--remove-paragraph-spacing set PDFInputfileParameters= set EpubInternalHTMLsplitSize=--flow-size 50 IF "%~x1"=="PDF" set PDFInputfileParameters=--unwrap-factor 0.25 set outputFileName=%~dp1%autore% - %titolo%.epub REM If filename alrteady exist, use a different name (I'll append current date/time to create a uinique filename) IF EXIST "%outputFileName%" ( rem Get current date SET DT=%date% rem remove character invalid for filename SET DT=!DT:\=_! SET DT=!DT:/=_! SET DT=!DT:-=_! SET DT=!DT:.=_! SET DT=!DT::=_! rem Get current time SET TM=%time% rem remove character invalid for filename SET TM=!TM:\=.! SET TM=!TM:/=.! SET TM=!TM:-=.! SET TM=!TM::=.! set outputFileName=%~dp1%autore% - %titolo% !DT! !TM!.epub ) "%scriptPath%\ebook-convert.exe" "%~1" "%outputFileName%" %InputfileParameters% %PDFInputfileParameters% %EpubInternalHTMLsplitSize% --authors "%autore%" --title "%titolo%" IF %ERRORLEVEL% NEQ 0 pause rem end of sub function goto :eof rem end of the batch file :endOfBatchfile

This batch file is very similar to the old one.
All the main code to do the conversion is included in a Sub function called epubConvert.
At the beginning of the file I've added about 400 lines of command like Switch / Call epubConvert %1- Switch / Call epubConvert %1 - Switch / Call epubConvert %1 - ...


By implementing these feature I even discovered a new batch command (shift) that I've never used before, it's very useful if you need to manage more than 9 command line parameters :-)