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