quote:

---

Originally posted by JerryHD1

Select obj_usr_num
into #mytable
from some_obj
where obj_usr_num between '2011-10-01 00:00:00.000' and '2013-10-01 00:00:00.000'
Order by obj_usr_num

Select obj_usr_num
from #mytable

JD2313-10-R-0002-P00002
K33312-13-L-0001
S22133-12-W-0004-P00001
S22133-12-Z-0001
QQQ123-11-Q-0004
PPP123-12-M-0008-P00003
PPP123-12-K-0010
PPP123-12-L-0001
PPP123-98-D-0001
PPPXXX-07-L-0003-P00002


SELECT PARSENAME(REPLACE(obj_usr_num,'-','.'),4) AS first6,
PARSENAME(REPLACE(obj_usr_num,'-','.'),3) AS Next2,
PARSENAME(REPLACE(obj_usr_num,'-','.'),2) AS IgnorePart,
PARSENAME(REPLACE(obj_usr_num,'-','.'),1) AS lastPart
FROM #mytable

Is this what you are looking for? Again, the last - and 6 characters do not matter, if exist. Only concerned with information up to the first character of the 4. SO information could stop at xxxxxx-xx-x-x. The remaining 003-P00002 is not included in count.


Jerry Haskins

---

quote:

---

...is there a way to use something like oracles RPAD or something similar to pad 3 additional 000 to the right of the series result

---

quote:

---

Originally posted by JerryHD1

I've tried several placements of the replicate function, but still not working. Not sure how to place it or where, in order to pad 3 000 onto the second substring result. Right now it results to 0 I need it to be padded with 000 to the right. If result is 0 then should display 0000, if result is 4, it should display as 4000. I am guessing that the padding should be in both the select statement and group by, is this correct?


SELECT LEFT(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX('-',obj_usr_num) ELSE LEN(obj_usr_num) + 1 END -1) AS [DODAAC/UIC],
SUBSTRING(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX('-',obj_usr_num) + 1 ELSE NULL END,2) AS FY,
SUBSTRING(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX('-',obj_usr_num) + 6 ELSE NULL END,1),
COUNT(*) AS [Award Count]
FROM #awardtable
GROUP BY LEFT(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX('-',obj_usr_num) ELSE LEN(obj_usr_num) + 1 END -1),
SUBSTRING(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX('-',obj_usr_num) + 1 ELSE NULL END,2),
SUBSTRING(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX ('-',obj_usr_num) + 6 ELSE NULL END,1)

Results now are:
DODAAC/UIC, FY, Series, Award Count
JD2313, 10, 0, 1
K33312, 13, 0, 1
S22133, 12, 0, 2
QQQ123, 11, 0, 1
PPP123, 12, 0, 3
PPP123, 98, 0, 1
PPPXXX, 07, 0, 1

Although third column series is 0, it could be 2, 4, 5, 8 with padding would be 2000, 4000, 5000, 8000. Hoping the replicate function would do this, such as RIGHT(REPLICATE('0', 3)

I need it to read:
DODAAC/UIC, FY, Series, Award Count
JD2313, 10, 0000, 1
K33312, 13, 0000, 1
S22133, 12, 0000, 2
QQQ123, 11, 0000, 1
PPP123, 12, 0000, 3
PPP123, 98, 0000, 1
PPPXXX, 07, 0000, 1

Thanks,
Jerry Haskins

---

quote:

---

Originally posted by JerryHD1

I've tried several placements of the replicate function, but still not working. Not sure how to place it or where, in order to pad 3 000 onto the second substring result. Right now it results to 0 I need it to be padded with 000 to the right. If result is 0 then should display 0000, if result is 4, it should display as 4000. I am guessing that the padding should be in both the select statement and group by, is this correct?


SELECT LEFT(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX('-',obj_usr_num) ELSE LEN(obj_usr_num) + 1 END -1) AS [DODAAC/UIC],
LEFT(SUBSTRING(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX('-',obj_usr_num) + 1 ELSE NULL END,2) + REPLICATE('0',4),4) AS FY,
SUBSTRING(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX('-',obj_usr_num) + 6 ELSE NULL END,1),
COUNT(*) AS [Award Count]
FROM #awardtable
GROUP BY LEFT(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX('-',obj_usr_num) ELSE LEN(obj_usr_num) + 1 END -1),
LEFT(SUBSTRING(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX('-',obj_usr_num) + 1 ELSE NULL END,2) + REPLICATE('0',4),4),
SUBSTRING(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX ('-',obj_usr_num) + 6 ELSE NULL END,1)

Results now are:
DODAAC/UIC, FY, Series, Award Count
JD2313, 10, 0, 1
K33312, 13, 0, 1
S22133, 12, 0, 2
QQQ123, 11, 0, 1
PPP123, 12, 0, 3
PPP123, 98, 0, 1
PPPXXX, 07, 0, 1

Although third column series is 0, it could be 2, 4, 5, 8 with padding would be 2000, 4000, 5000, 8000. Hoping the replicate function would do this, such as RIGHT(REPLICATE('0', 3)

I need it to read:
DODAAC/UIC, FY, Series, Award Count
JD2313, 10, 0000, 1
K33312, 13, 0000, 1
S22133, 12, 0000, 2
QQQ123, 11, 0000, 1
PPP123, 12, 0000, 3
PPP123, 98, 0000, 1
PPPXXX, 07, 0000, 1

Thanks,
Jerry Haskins

---

--===== Conditionally drop the test table to make reruns in SSMS easier.
     IF OBJECT_ID('tempdb..#MyHead','U') IS NOT NULL DROP TABLE #MyHead;

--===== Build and populate the test table on the fly.
 SELECT *
   INTO #MyHead
   FROM (
         SELECT 'JD2313-10-R-0002' UNION ALL
         SELECT 'K33312-13-L-0001' UNION ALL
         SELECT 'S22133-12-W-0004' UNION ALL
         SELECT 'S22133-12-Z-0001' UNION ALL
         SELECT 'QQQ123-11-Q-0004' UNION ALL
         SELECT 'PPP123-12-M-0008' UNION ALL
         SELECT 'PPP123-12-K-0010' UNION ALL
         SELECT 'PPP123-12-L-0001' UNION ALL
         SELECT 'PPP123-98-D-0001' UNION ALL
         SELECT 'PPPXXX-07-L-0003' UNION ALL
         SELECT 'JD2313-10-R-0002-P00002' UNION ALL
         SELECT 'K33312-13-L-0001' UNION ALL 
         SELECT 'S22133-12-W-1004-P00001' UNION ALL
         SELECT 'S22133-12-Z-1001' UNION ALL 
         SELECT 'QQQ123-11-Q-2004' UNION ALL 
         SELECT 'PPP123-12-M-2008-P00003' UNION ALL 
         SELECT 'PPP123-12-K-2010' UNION ALL 
         SELECT 'PPP123-12-L-3001' UNION ALL 
         SELECT 'PPP123-98-D-5001' UNION ALL 
         SELECT 'PPPXXX-07-L-1003-P00002'
        ) d (obj_usr_num)
;

--===== Solve the given problem
WITH 
cteParse AS
(
 SELECT [DODAAC/UIC] = SUBSTRING(obj_usr_num,1,6)
,       FY           = SUBSTRING(obj_usr_num,8,2)
,       Series       = SUBSTRING(obj_usr_num,13,1) + '000'
   FROM #MyHead
)
 SELECT [DODAAC/UIC]
,       FY
,       Series
,       [Award Count] = COUNT(*)
   FROM cteParse
  GROUP BY [DODAAC/UIC],FY,Series
  ORDER BY [DODAAC/UIC],FY,Series

DODAAC/UIC FY   Series Award Count
---------- ---- ------ -----------
JD2313     10   0000   2
K33312     13   0000   2
PPP123     12   0000   3
PPP123     12   2000   2
PPP123     12   3000   1
PPP123     98   0000   1
PPP123     98   5000   1
PPPXXX     07   0000   1
PPPXXX     07   1000   1
QQQ123     11   0000   1
QQQ123     11   2000   1
S22133     12   0000   2
S22133     12   1000   2

(13 row(s) affected)

quote:

---

Originally posted by JerryHD1

Since I've seen no advice given...

---

quote:

---

Originally posted by JerryHD1

I did see your post and I responded, but since I saw no reply's, I desided to post again. I had stated that there is an error with the WITH Keyword. Apparently, Sybase does not like it.

I guess I am not understand what you consider readily consummable data. I took this to be readily consummable date.

create table #awardtable (obj_usr_num varchar(30) null)

-- Seed the test table


insert #awardtable values ( 'JD2313-10-R-0002' )
insert #awardtable values ( 'K33312-13-L-0001' )
insert #awardtable values ( 'K33312-13-L-0001-1000' )
insert #awardtable values ( 'K33312-13-L-2001-4000' )
insert #awardtable values ( 'S22133-12-W-0004' )
insert #awardtable values ( 'S22133-12-Z-0001' )
insert #awardtable values ( 'QQQ123-11-Q-0004' )
insert #awardtable values ( 'PPP123-12-M-0008' )
insert #awardtable values ( 'PPP123-12-K-0010' )
insert #awardtable values ( 'PPP123-98-D-0001' )
insert #awardtable values ( 'JD2313-10-R-0002-P00002' )
insert #awardtable values ( 'S22133-12-W-1004-P00001' )
insert #awardtable values ( 'S22133-12-Z-1001' )
insert #awardtable values ( 'QQQ123-11-Q-2004' )
insert #awardtable values ( 'PPP123-12-M-2008-P00003' )
insert #awardtable values ( 'PPPXXX-07-L-1003-P00002' )

---