April 17, 2009 at 4:21 am
split column field value 2:1 or 25:13 as 2 1 or 25 and 13.
😀
April 17, 2009 at 4:32 am
- there are a number of split functions available at SSC.
- or you can use a combination of :
e.g.
left(yourcol,charindex(':', yourcol)-1)
and
right(yourcol,datalength(yourcol) - charindex(':', yourcol)+1)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 17, 2009 at 8:27 am
Hi
You can use below script:
DECLARE @t TABLE (split_col VARCHAR(100))
INSERT INTO @t VALUES ('3:16')
SELECT split_col,
SUBSTRING(split_col, 1, CHARINDEX(':', split_col)) First_val,
SUBSTRING(split_col, CHARINDEX(':', split_col) + 1, 100) Second_val
FROM @t
April 19, 2009 at 10:29 pm
Thank You.
Very cool !!!!
😀
April 20, 2009 at 8:28 am
Glad you got the answer..
January 27, 2010 at 2:26 pm
The following string exists in a filter field called 'dimfilter' in a table called userfilters
DP1201|DP1210|DP1220|DP1230|DP1240|DP1500|DP1510|DP1520|DP1530|DP2301|DP2310..DP2320|DP2340|DP2700|P2710|DP6010|DP6020|DP7000..DP7999
I would like to be able to do the following type of query against these values:
select DEPARTMENT from DIMENSION where DEPARTMENT in (select dimfilter from userfilter) or DEPARTMENT between (select case when value2<> ' ' then value1 end ) and (select case when value2<> ' ' then value2)
the issue is that i need a table to query against each time I do the filter testing and there are two delimiters, '|' and '..'
the '|' delimits single values, the '..' is for from .. to.
How can I create a temporary table each time which will list the single values in a row and the second value after the .. (DP2310..DP2320 or DP7000..DP7999) in a second column?
I have seen so many split functions and none of those deal with multiple delimiters over several rows.
I would really appreciate a solution!
January 28, 2010 at 12:15 am
- removed because of stupidity - to early in the morning :hehe:
[Second Edit]
... after the furst cup of coffee :w00t:
Did you try this ?
/*
dd 20100128
http://qa.sqlservercentral.com/Forums/Topic699260-1291-1.aspx
How can I create a temporary table each time which will list the single values
in a row and the second value after the .. (DP2310..DP2320 or DP7000..DP7999) in a second column?
*/
declare @s1 varchar(1000)
Declare @SplitChar varchar(15)
Declare @RangeChar varchar(15)
select @s1 = 'DP1201|DP1210|DP1220|DP1230|DP1240|DP1500|DP1510|DP1520|DP1530|DP2301|DP2310..DP2320|DP2340|DP2700|P2710|DP6010|DP6020|DP7000..DP7999'
, @SplitChar = '|'
, @RangeChar = '..'
;
Declare @tmpResult table ( ItemId int NOT NULL PRIMARY KEY IDENTITY(1, 1)
, Item varchar(4000) NULL
) ;
with cteSplit ( i, j, ParseResult )
as (
select 1
, charindex(@SplitChar, @s1 + @SplitChar)
, substring(@s1, 1, charindex(@SplitChar, @s1 + @SplitChar) - 1)
union all
select j + 1
, charindex(@SplitChar, @s1 + @SplitChar, j + 1)
, substring(@s1, j + 1, charindex(@SplitChar, @s1 + @SplitChar, j + 1) - ( j + 1 ))
from cteSplit
where charindex(@SplitChar, @s1 + @SplitChar, j + 1) <> 0
)
, cteSplitRanges
as ( Select *
, substring( ParseResult , 1, charindex(@RangeChar, ParseResult + @RangeChar) - 1 ) as StartItem
, substring( ParseResult , charindex(@RangeChar, ParseResult + @RangeChar) + 2 , datalength( ParseResult ) ) as EndItem
from cteSplit
Where ParseResult like '%' + @RangeChar + '%' )
, cteSplitSplitted
as ( Select substring( StartItem , 1, patindex('%[0-9]%', StartItem) - 1 ) as StartString
, substring( StartItem, patindex('%[0-9]%', StartItem), datalength( StartItem ) ) as StartInt
, substring( EndItem, patindex('%[0-9]%', EndItem), datalength( EndItem ) ) as EndInt
from cteSplitRanges
)
Insert into @tmpResult ( Item )
Select ParseResult
from cteSplit
Where ParseResult NOT like '%' + @RangeChar + '%'
UNION
Select StartString + CONVERT(varchar(15), N)
from cteSplitSplitted
/* http://qa.sqlservercentral.com/articles/T-SQL/67899/ By Lynn Pettis, 2009/09/22 */
cross apply master.dbo.fn_DBA_Tally (StartInt, EndInt, 1)
order by 1 ;
Select *
from @tmpResult
order by ItemId ;
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 28, 2010 at 7:29 am
Hi there,
I have not tried your solution, thanks, I will most certainly....
Have tried a function which gives me the result in two columns, but repeats the first value in the second, except when the delimiter = .. (see very last row)
Unfortunately the issue now I tangled myself into another problem, testing each value from the dimension table against each row in this table as a "between Value1 and Value2... aaaahhhh.
How do I make a script cycle through these rows now? Down the rabbit hole it goes.......
user value1 value2
DOMAIN\user DP1201 DP1201
DOMAIN\user DP1210 DP1210
DOMAIN\user DP1220 DP1220
DOMAIN\user DP1230 DP1230
DOMAIN\user DP1240 DP1240
DOMAIN\user DP1500 DP1500
DOMAIN\user DP1510 DP1510
DOMAIN\user DP1520 DP1520
DOMAIN\user DP1530 DP1530
DOMAIN\user DP2301 DP2301
DOMAIN\user DP2310 DP2310
DOMAIN\user DP2320 DP2320
DOMAIN\user DP2340 DP2340
DOMAIN\user DP27000 DP27000
DOMAIN\user DP7000 DP7999
January 28, 2010 at 7:53 am
Hi there,
I have tried the code from your reply, but unfortunately the result set seems to add values which are not in the string...
1 DP1201
2 DP1210
3 DP1220
4 DP1230
5 DP1240
6 DP1500
7 DP1510
8 DP1520
9 DP1530
10 DP2301
11 DP2310
12 DP2320
13 DP2340
14 DP2700
15 DP6010
16 DP6020
17 DP7000
18 DP7001
19 DP7002
20 DP7003
21 DP7004
22 DP7005
23 DP7006
24 DP7007
25 DP7008
26 DP7009
27 DP7010
28 DP7011
29 DP7012
30 DP7013
31 DP7014
32 DP7015
33 DP7016
34 DP7017
35 DP7018
36 DP7019
37 DP7020
38 DP7021
39 DP7022
40 DP7023
41 DP7024
42 DP7025
43 DP7026
44 DP7027
45 DP7028
46 DP7029
47 DP7030
48 DP7031
49 DP7032
50 DP7033
51 DP7034
52 DP7035
53 DP7036
54 DP7037
55 DP7038
56 DP7039
57 DP7040
58 DP7041
59 DP7042
60 DP7043
61 DP7044
62 DP7045
63 DP7046
64 DP7047
65 DP7048
66 DP7049
67 DP7050
68 DP7051
69 DP7052
70 DP7053
71 DP7054
72 DP7055
73 DP7056
74 DP7057
75 DP7058
76 DP7059
77 DP7060
78 DP7061
79 DP7062
80 DP7063
81 DP7064
82 DP7065
83 DP7066
84 DP7067
85 DP7068
86 DP7069
87 DP7070
88 DP7071
89 DP7072
90 DP7073
91 DP7074
92 DP7075
93 DP7076
94 DP7077
95 DP7078
96 DP7079
97 DP7080
98 DP7081
99 DP7082
100 DP7083
101 DP7084
102 DP7085
103 DP7086
104 DP7087
105 DP7088
106 DP7089
107 DP7090
108 DP7091
109 DP7092
110 DP7093
111 DP7094
112 DP7095
113 DP7096
114 DP7097
115 DP7098
116 DP7099
117 DP7100
118 DP7101
119 DP7102
120 DP7103
121 DP7104
122 DP7105
123 DP7106
124 DP7107
125 DP7108
126 DP7109
127 DP7110
128 DP7111
129 DP7112
130 DP7113
131 DP7114
132 DP7115
133 DP7116
134 DP7117
135 DP7118
136 DP7119
137 DP7120
138 DP7121
139 DP7122
140 DP7123
141 DP7124
142 DP7125
143 DP7126
144 DP7127
145 DP7128
146 DP7129
147 DP7130
148 DP7131
149 DP7132
150 DP7133
151 DP7134
152 DP7135
153 DP7136
154 DP7137
155 DP7138
156 DP7139
157 DP7140
158 DP7141
159 DP7142
160 DP7143
161 DP7144
162 DP7145
163 DP7146
164 DP7147
165 DP7148
166 DP7149
167 DP7150
168 DP7151
169 DP7152
170 DP7153
171 DP7154
172 DP7155
173 DP7156
174 DP7157
175 DP7158
176 DP7159
177 DP7160
178 DP7161
179 DP7162
180 DP7163
181 DP7164
182 DP7165
183 DP7166
184 DP7167
185 DP7168
186 DP7169
187 DP7170
188 DP7171
189 DP7172
190 DP7173
191 DP7174
192 DP7175
193 DP7176
194 DP7177
195 DP7178
196 DP7179
197 DP7180
198 DP7181
199 DP7182
200 DP7183
201 DP7184
202 DP7185
203 DP7186
204 DP7187
205 DP7188
206 DP7189
207 DP7190
208 DP7191
209 DP7192
210 DP7193
211 DP7194
212 DP7195
213 DP7196
214 DP7197
215 DP7198
216 DP7199
217 DP7200
218 DP7201
219 DP7202
220 DP7203
221 DP7204
222 DP7205
223 DP7206
224 DP7207
225 DP7208
226 DP7209
227 DP7210
228 DP7211
229 DP7212
230 DP7213
231 DP7214
232 DP7215
233 DP7216
234 DP7217
235 DP7218
236 DP7219
237 DP7220
238 DP7221
239 DP7222
240 DP7223
241 DP7224
242 DP7225
243 DP7226
244 DP7227
245 DP7228
246 DP7229
247 DP7230
248 DP7231
249 DP7232
250 DP7233
251 DP7234
252 DP7235
253 DP7236
254 DP7237
255 DP7238
256 DP7239
257 DP7240
258 DP7241
259 DP7242
260 DP7243
261 DP7244
262 DP7245
263 DP7246
264 DP7247
265 DP7248
266 DP7249
267 DP7250
268 DP7251
269 DP7252
270 DP7253
271 DP7254
272 DP7255
273 DP7256
274 DP7257
275 DP7258
276 DP7259
277 DP7260
278 DP7261
279 DP7262
280 DP7263
281 DP7264
282 DP7265
283 DP7266
284 DP7267
285 DP7268
286 DP7269
287 DP7270
288 DP7271
289 DP7272
290 DP7273
291 DP7274
292 DP7275
293 DP7276
294 DP7277
295 DP7278
296 DP7279
297 DP7280
298 DP7281
299 DP7282
300 DP7283
301 DP7284
302 DP7285
303 DP7286
304 DP7287
305 DP7288
306 DP7289
307 DP7290
308 DP7291
309 DP7292
310 DP7293
311 DP7294
312 DP7295
313 DP7296
314 DP7297
315 DP7298
316 DP7299
317 DP7300
318 DP7301
319 DP7302
320 DP7303
321 DP7304
322 DP7305
323 DP7306
324 DP7307
325 DP7308
326 DP7309
327 DP7310
328 DP7311
329 DP7312
330 DP7313
331 DP7314
332 DP7315
333 DP7316
334 DP7317
335 DP7318
336 DP7319
337 DP7320
338 DP7321
339 DP7322
340 DP7323
341 DP7324
342 DP7325
343 DP7326
344 DP7327
345 DP7328
346 DP7329
347 DP7330
348 DP7331
349 DP7332
350 DP7333
351 DP7334
352 DP7335
353 DP7336
354 DP7337
355 DP7338
356 DP7339
357 DP7340
358 DP7341
359 DP7342
360 DP7343
361 DP7344
362 DP7345
363 DP7346
364 DP7347
365 DP7348
366 DP7349
367 DP7350
368 DP7351
369 DP7352
370 DP7353
371 DP7354
372 DP7355
373 DP7356
374 DP7357
375 DP7358
376 DP7359
377 DP7360
378 DP7361
379 DP7362
380 DP7363
381 DP7364
382 DP7365
383 DP7366
384 DP7367
385 DP7368
386 DP7369
387 DP7370
388 DP7371
389 DP7372
390 DP7373
391 DP7374
392 DP7375
393 DP7376
394 DP7377
395 DP7378
396 DP7379
397 DP7380
398 DP7381
399 DP7382
400 DP7383
401 DP7384
402 DP7385
403 DP7386
404 DP7387
405 DP7388
406 DP7389
407 DP7390
408 DP7391
409 DP7392
410 DP7393
411 DP7394
412 DP7395
413 DP7396
414 DP7397
415 DP7398
416 DP7399
417 DP7400
418 DP7401
419 DP7402
420 DP7403
421 DP7404
422 DP7405
423 DP7406
424 DP7407
425 DP7408
426 DP7409
427 DP7410
428 DP7411
429 DP7412
430 DP7413
431 DP7414
432 DP7415
433 DP7416
434 DP7417
435 DP7418
436 DP7419
437 DP7420
438 DP7421
439 DP7422
440 DP7423
441 DP7424
442 DP7425
443 DP7426
444 DP7427
445 DP7428
446 DP7429
447 DP7430
448 DP7431
449 DP7432
450 DP7433
451 DP7434
452 DP7435
453 DP7436
454 DP7437
455 DP7438
456 DP7439
457 DP7440
458 DP7441
459 DP7442
460 DP7443
461 DP7444
462 DP7445
463 DP7446
464 DP7447
465 DP7448
466 DP7449
467 DP7450
468 DP7451
469 DP7452
470 DP7453
471 DP7454
472 DP7455
473 DP7456
474 DP7457
475 DP7458
476 DP7459
477 DP7460
478 DP7461
479 DP7462
480 DP7463
481 DP7464
482 DP7465
483 DP7466
484 DP7467
485 DP7468
486 DP7469
487 DP7470
488 DP7471
489 DP7472
490 DP7473
491 DP7474
492 DP7475
493 DP7476
494 DP7477
495 DP7478
496 DP7479
497 DP7480
498 DP7481
499 DP7482
500 DP7483
501 DP7484
502 DP7485
503 DP7486
504 DP7487
505 DP7488
506 DP7489
507 DP7490
508 DP7491
509 DP7492
510 DP7493
511 DP7494
512 DP7495
513 DP7496
514 DP7497
515 DP7498
516 DP7499
517 DP7500
518 DP7501
519 DP7502
520 DP7503
521 DP7504
522 DP7505
523 DP7506
524 DP7507
525 DP7508
526 DP7509
527 DP7510
528 DP7511
529 DP7512
530 DP7513
531 DP7514
532 DP7515
533 DP7516
534 DP7517
535 DP7518
536 DP7519
537 DP7520
538 DP7521
539 DP7522
540 DP7523
541 DP7524
542 DP7525
543 DP7526
544 DP7527
545 DP7528
546 DP7529
547 DP7530
548 DP7531
549 DP7532
550 DP7533
551 DP7534
552 DP7535
553 DP7536
554 DP7537
555 DP7538
556 DP7539
557 DP7540
558 DP7541
559 DP7542
560 DP7543
561 DP7544
562 DP7545
563 DP7546
564 DP7547
565 DP7548
566 DP7549
567 DP7550
568 DP7551
569 DP7552
570 DP7553
571 DP7554
572 DP7555
573 DP7556
574 DP7557
575 DP7558
576 DP7559
577 DP7560
578 DP7561
579 DP7562
580 DP7563
581 DP7564
582 DP7565
583 DP7566
584 DP7567
585 DP7568
586 DP7569
587 DP7570
588 DP7571
589 DP7572
590 DP7573
591 DP7574
592 DP7575
593 DP7576
594 DP7577
595 DP7578
596 DP7579
597 DP7580
598 DP7581
599 DP7582
600 DP7583
601 DP7584
602 DP7585
603 DP7586
604 DP7587
605 DP7588
606 DP7589
607 DP7590
608 DP7591
609 DP7592
610 DP7593
611 DP7594
612 DP7595
613 DP7596
614 DP7597
615 DP7598
616 DP7599
617 DP7600
618 DP7601
619 DP7602
620 DP7603
621 DP7604
622 DP7605
623 DP7606
624 DP7607
625 DP7608
626 DP7609
627 DP7610
628 DP7611
629 DP7612
630 DP7613
631 DP7614
632 DP7615
633 DP7616
634 DP7617
635 DP7618
636 DP7619
637 DP7620
638 DP7621
639 DP7622
640 DP7623
641 DP7624
642 DP7625
643 DP7626
644 DP7627
645 DP7628
646 DP7629
647 DP7630
648 DP7631
649 DP7632
650 DP7633
651 DP7634
652 DP7635
653 DP7636
654 DP7637
655 DP7638
656 DP7639
657 DP7640
658 DP7641
659 DP7642
660 DP7643
661 DP7644
662 DP7645
663 DP7646
664 DP7647
665 DP7648
666 DP7649
667 DP7650
668 DP7651
669 DP7652
670 DP7653
671 DP7654
672 DP7655
673 DP7656
674 DP7657
675 DP7658
676 DP7659
677 DP7660
678 DP7661
679 DP7662
680 DP7663
681 DP7664
682 DP7665
683 DP7666
684 DP7667
685 DP7668
686 DP7669
687 DP7670
688 DP7671
689 DP7672
690 DP7673
691 DP7674
692 DP7675
693 DP7676
694 DP7677
695 DP7678
696 DP7679
697 DP7680
698 DP7681
699 DP7682
700 DP7683
701 DP7684
702 DP7685
703 DP7686
704 DP7687
705 DP7688
706 DP7689
707 DP7690
708 DP7691
709 DP7692
710 DP7693
711 DP7694
712 DP7695
713 DP7696
714 DP7697
715 DP7698
716 DP7699
717 DP7700
718 DP7701
719 DP7702
720 DP7703
721 DP7704
722 DP7705
723 DP7706
724 DP7707
725 DP7708
726 DP7709
727 DP7710
728 DP7711
729 DP7712
730 DP7713
731 DP7714
732 DP7715
733 DP7716
734 DP7717
735 DP7718
736 DP7719
737 DP7720
738 DP7721
739 DP7722
740 DP7723
741 DP7724
742 DP7725
743 DP7726
744 DP7727
745 DP7728
746 DP7729
747 DP7730
748 DP7731
749 DP7732
750 DP7733
751 DP7734
752 DP7735
753 DP7736
754 DP7737
755 DP7738
756 DP7739
757 DP7740
758 DP7741
759 DP7742
760 DP7743
761 DP7744
762 DP7745
763 DP7746
764 DP7747
765 DP7748
766 DP7749
767 DP7750
768 DP7751
769 DP7752
770 DP7753
771 DP7754
772 DP7755
773 DP7756
774 DP7757
775 DP7758
776 DP7759
777 DP7760
778 DP7761
779 DP7762
780 DP7763
781 DP7764
782 DP7765
783 DP7766
784 DP7767
785 DP7768
786 DP7769
787 DP7770
788 DP7771
789 DP7772
790 DP7773
791 DP7774
792 DP7775
793 DP7776
794 DP7777
795 DP7778
796 DP7779
797 DP7780
798 DP7781
799 DP7782
800 DP7783
801 DP7784
802 DP7785
803 DP7786
804 DP7787
805 DP7788
806 DP7789
807 DP7790
808 DP7791
809 DP7792
810 DP7793
811 DP7794
812 DP7795
813 DP7796
814 DP7797
815 DP7798
816 DP7799
817 DP7800
818 DP7801
819 DP7802
820 DP7803
821 DP7804
822 DP7805
823 DP7806
824 DP7807
825 DP7808
826 DP7809
827 DP7810
828 DP7811
829 DP7812
830 DP7813
831 DP7814
832 DP7815
833 DP7816
834 DP7817
835 DP7818
836 DP7819
837 DP7820
838 DP7821
839 DP7822
840 DP7823
841 DP7824
842 DP7825
843 DP7826
844 DP7827
845 DP7828
846 DP7829
847 DP7830
848 DP7831
849 DP7832
850 DP7833
851 DP7834
852 DP7835
853 DP7836
854 DP7837
855 DP7838
856 DP7839
857 DP7840
858 DP7841
859 DP7842
860 DP7843
861 DP7844
862 DP7845
863 DP7846
864 DP7847
865 DP7848
866 DP7849
867 DP7850
868 DP7851
869 DP7852
870 DP7853
871 DP7854
872 DP7855
873 DP7856
874 DP7857
875 DP7858
876 DP7859
877 DP7860
878 DP7861
879 DP7862
880 DP7863
881 DP7864
882 DP7865
883 DP7866
884 DP7867
885 DP7868
886 DP7869
887 DP7870
888 DP7871
889 DP7872
890 DP7873
891 DP7874
892 DP7875
893 DP7876
894 DP7877
895 DP7878
896 DP7879
897 DP7880
898 DP7881
899 DP7882
900 DP7883
901 DP7884
902 DP7885
903 DP7886
904 DP7887
905 DP7888
906 DP7889
907 DP7890
908 DP7891
909 DP7892
910 DP7893
911 DP7894
912 DP7895
913 DP7896
914 DP7897
915 DP7898
916 DP7899
917 DP7900
918 DP7901
919 DP7902
920 DP7903
921 DP7904
922 DP7905
923 DP7906
924 DP7907
925 DP7908
926 DP7909
927 DP7910
928 DP7911
929 DP7912
930 DP7913
931 DP7914
932 DP7915
933 DP7916
934 DP7917
935 DP7918
936 DP7919
937 DP7920
938 DP7921
939 DP7922
940 DP7923
941 DP7924
942 DP7925
943 DP7926
944 DP7927
945 DP7928
946 DP7929
947 DP7930
948 DP7931
949 DP7932
950 DP7933
951 DP7934
952 DP7935
953 DP7936
954 DP7937
955 DP7938
956 DP7939
957 DP7940
958 DP7941
959 DP7942
960 DP7943
961 DP7944
962 DP7945
963 DP7946
964 DP7947
965 DP7948
966 DP7949
967 DP7950
968 DP7951
969 DP7952
970 DP7953
971 DP7954
972 DP7955
973 DP7956
974 DP7957
975 DP7958
976 DP7959
977 DP7960
978 DP7961
979 DP7962
980 DP7963
981 DP7964
982 DP7965
983 DP7966
984 DP7967
985 DP7968
986 DP7969
987 DP7970
988 DP7971
989 DP7972
990 DP7973
991 DP7974
992 DP7975
993 DP7976
994 DP7977
995 DP7978
996 DP7979
997 DP7980
998 DP7981
999 DP7982
1000 DP7983
1001 DP7984
1002 DP7985
1003 DP7986
1004 DP7987
1005 DP7988
1006 DP7989
1007 DP7990
1008 DP7991
1009 DP7992
1010 DP7993
1011 DP7994
1012 DP7995
1013 DP7996
1014 DP7997
1015 DP7998
1016 DP7999
1017 P2710
January 28, 2010 at 8:03 am
Keep in mind HOW a between works with string values !!
Here's another way to get started ...
-- TEST IT -- TEST IT -- AND SEE WHAT IT DOES !!!
Declare @InputTable table (UserName varchar(128) not null, Value1 varchar(15) not null, Value2 varchar(15) not null )
Insert into @InputTable
select 'DOMAIN\user','DP1201','DP1201'
union all select 'DOMAIN\user','DP1210','DP1210'
union all select 'DOMAIN\user','DP1220','DP1220'
union all select 'DOMAIN\user','DP1230','DP1230'
union all select 'DOMAIN\user','DP1240','DP1240'
union all select 'DOMAIN\user','DP1500','DP1500'
union all select 'DOMAIN\user','DP1510','DP1510'
union all select 'DOMAIN\user','DP1520','DP1520'
union all select 'DOMAIN\user','DP1530','DP1530'
union all select 'DOMAIN\user','DP2301','DP2301'
union all select 'DOMAIN\user','DP2310','DP2310'
union all select 'DOMAIN\user','DP2320','DP2320'
union all select 'DOMAIN\user','DP2340','DP2340'
union all select 'DOMAIN\user','DP27000','DP27000'
union all select 'DOMAIN\user','DP7000','DP7999'
Select UserName , StartString + CONVERT(varchar(15), N)
from
(Select *
, substring( Value1 , 1, patindex('%[0-9]%', Value1) - 1 ) as StartString
, substring( Value1, patindex('%[0-9]%', Value1), datalength( Value1 ) ) as StartInt
, substring( Value2, patindex('%[0-9]%', Value2), datalength( Value2 ) ) as EndInt
from @InputTable
) I
/* http://qa.sqlservercentral.com/articles/T-SQL/67899/ By Lynn Pettis, 2009/09/22 */
cross apply master.dbo.fn_DBA_Tally (StartInt, EndInt, 1)
order by 1 ;
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 28, 2010 at 7:33 pm
Hi there
I will give it a try and let you know. Thanks for the reply.
January 29, 2010 at 1:59 am
As you have seen I prefer these kind of "lookup" values in table rows.
If you really need the "original" parsed value, on top of the actual individual value, just extend the table with an extra column (itemdetail) on which you will query later on, and still be able to return the parsed value (individual or range !)
/*
dd 20100128
http://qa.sqlservercentral.com/Forums/Topic699260-1291-1.aspx
How can I create a temporary table each time which will list the single values
in a row and the second value after the .. (DP2310..DP2320 or DP7000..DP7999) in a second column?
*/
declare @s1 varchar(1000)
Declare @SplitChar varchar(15)
Declare @RangeChar varchar(15)
select @s1 = 'DP1201|DP1210|DP1220|DP1230|DP1240|DP1500|DP1510|DP1520|DP1530|DP2301|DP2310..DP2320|DP2340|DP2700|P2710|DP6010|DP6020|DP7000..DP7999'
, @SplitChar = '|'
, @RangeChar = '..'
;
Declare @tmpResult table ( ItemId int NOT NULL PRIMARY KEY IDENTITY(1, 1)
, Item varchar(4000) NULL
, ItemDetail varchar(4000) null
) ;
with cteSplit ( i, j, ParseResult )
as (
select 1
, charindex(@SplitChar, @s1 + @SplitChar)
, substring(@s1, 1, charindex(@SplitChar, @s1 + @SplitChar) - 1)
union all
select j + 1
, charindex(@SplitChar, @s1 + @SplitChar, j + 1)
, substring(@s1, j + 1, charindex(@SplitChar, @s1 + @SplitChar, j + 1) - ( j + 1 ))
from cteSplit
where charindex(@SplitChar, @s1 + @SplitChar, j + 1) <> 0
)
, cteSplitRanges
as ( Select *
, substring( ParseResult , 1, charindex(@RangeChar, ParseResult + @RangeChar) - 1 ) as StartItem
, substring( ParseResult , charindex(@RangeChar, ParseResult + @RangeChar) + 2 , datalength( ParseResult ) ) as EndItem
from cteSplit
Where ParseResult like '%' + @RangeChar + '%' )
, cteSplitSplitted
as ( Select ParseResult
, substring( StartItem , 1, patindex('%[0-9]%', StartItem) - 1 ) as StartString
, substring( StartItem, patindex('%[0-9]%', StartItem), datalength( StartItem ) ) as StartInt
, substring( EndItem, patindex('%[0-9]%', EndItem), datalength( EndItem ) ) as EndInt
from cteSplitRanges
)
Insert into @tmpResult ( Item , ItemDetail )
Select ParseResult , ParseResult
from cteSplit
Where ParseResult NOT like '%' + @RangeChar + '%'
UNION
Select ParseResult, StartString + CONVERT(varchar(15), N)
from cteSplitSplitted
/* http://qa.sqlservercentral.com/articles/T-SQL/67899/ By Lynn Pettis, 2009/09/22 */
cross apply master.dbo.fn_DBA_Tally (StartInt, EndInt, 1)
order by 1 ;
Select *
from @tmpResult
order by ItemId ;
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 29, 2010 at 6:29 am
Hi,
This looks good, I will give it a test drive, thanks.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply