| Author |
 Topic  |
|
JerryHD1
Starting Member
USA
21 Posts |
 Posted - 01/12/2013 : 11:44:22
|
I do basic select statements based on simple from/where clauses. Need help trying to put together a query on results placed into #mytable. Actual results from the query I wrote is a single column of a obj_usr_num, which is simply a contract number. This is placed into #mytable.
obj_usr_num
JD2313-10-R-0002
K33312-13-L-0001
S22133-12-W-0004
S22133-12-Z-0001
QQQ123-11-Q-0004
PPP123-12-M-0008
PPP123-12-K-0010
PPP123-12-L-0001
PPP123-98-D-0001
PPPXXX-07-L-0003
So from these results, let’s look at the first 6 characters. Are the characters the same or Unique. For the next 2 characters are they the same or Unique. The next single character is not needed and can be ignored. The last 4 characters, do the begin with the same character or is it Unique. So the output should look like this.
1st 6 char Next 2 char Next char ignored Next 4 char Count
JD2313 10 Not Needed 0000 1
K33312 13 Not Needed 0000 1
S22133 12 Not Needed 0000 2
QQQ123 11 Not Needed 0000 1
PPP123 12 Not Needed 0000 3
PPP123 98 Not Needed 0000 1
PPPXXX 07 Not Needed 0000 1
The question is, how do you break out each separate part of the usr_num and then try to match the first 6 characters and then of those, which also have the same matching next 2 characters and then omit the next single character, and then of the next 4 charcters, which first character of those match and then produce the count as I've shown?
This part of the puzzle is beyond me. Anyone that can help, it would be greatly appreciated.
Thanks
Jerry Haskins |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47952 Posts |
Posted - 01/12/2013 : 12:31:56
|
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 [First6],
SUBSTRING(obj_usr_num,CASE WHEN CHARINDEX('-',obj_usr_num)>0 THEN CHARINDEX('-',obj_usr_num) + 1 ELSE NULL END,2) AS Next2,
COUNT(*) AS Cnt
FROM Table
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)
EDIT: Fixed typo
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
Edited by - visakh16 on 01/12/2013 12:45:59 |
 |
|
|
JerryHD1
Starting Member
USA
21 Posts |
Posted - 01/12/2013 : 12:41:18
|
WOW, you guys are fast around here. Thanks for the quick response. I am however, getting an error on line 5 of your script Incorrect syntax near the Keyword'AS'
Jerry Haskins |
Edited by - JerryHD1 on 01/12/2013 12:41:44 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47952 Posts |
Posted - 01/12/2013 : 12:46:29
|
quote:
Originally posted by JerryHD1
WOW, you guys are fast around here. Thanks for the quick response. I am however, getting an error on line 5 of your script Incorrect syntax near the Keyword'AS'
Jerry Haskins
can you try my edited response above
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
JerryHD1
Starting Member
USA
21 Posts |
Posted - 01/12/2013 : 13:03:38
|
Thanks, that seems to be working. I need to do a few test runs and tweek results a bit, but very appreciative of your quick response. I think I may need to show the last for characters or going by your script, you list the next2 then you skip the next 1 and then list the count. Between the next2 and Count, I need to skip the next 1, but list the next 4. Hope that makes sense.
Jerry Haskins |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47952 Posts |
Posted - 01/12/2013 : 13:04:51
|
is the format alway consistent? ie 4 parts separated by -?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
JerryHD1
Starting Member
USA
21 Posts |
Posted - 01/12/2013 : 13:08:05
|
The results are indeed consistent as follows. xxxxxx-xx-x-xxxx always the same format, however, the numbers can go out a bit further, but this is as far as I need to go out. Make sense?
Jerry Haskins |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47952 Posts |
Posted - 01/12/2013 : 13:11:09
|
if format is consistent a better way to do is
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 table
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
JerryHD1
Starting Member
USA
21 Posts |
Posted - 01/12/2013 : 13:17:21
|
With my limited knowledge, I can read that a bit easier than the other response. However, I am getting an Incorrect syntax near the Keyword REPLACE on line 1, so not sure if it will affect the other REPLACE keywords.
Jerry Haskins |
Edited by - JerryHD1 on 01/12/2013 13:18:11 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47952 Posts |
Posted - 01/12/2013 : 13:27:29
|
i'm not getting any error. can you show your full statement please?
declare @t table
(
obj_usr_num varchar(100)
)
insert @t
VALUES('JD2313-10-R-0002'),
('K33312-13-L-0001'),
('S22133-12-W-0004'),
('S22133-12-Z-0001'),
('QQQ123-11-Q-0004'),
('PPP123-12-M-0008'),
('PPP123-12-K-0010'),
('PPP123-12-L-0001'),
('PPP123-98-D-0001'),
('PPPXXX-07-L-0003')
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 @t
output
-----------------------------------------
first6 Next2 IgnorePart lastPart
-----------------------------------------
JD2313 10 R 0002
K33312 13 L 0001
S22133 12 W 0004
S22133 12 Z 0001
QQQ123 11 Q 0004
PPP123 12 M 0008
PPP123 12 K 0010
PPP123 12 L 0001
PPP123 98 D 0001
PPPXXX 07 L 0003
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Jeff Moden
Aged Yak Warrior
USA
644 Posts |
Posted - 01/12/2013 : 13:36:01
|
If you do't need the two pseudo columns to be separate, you can simplify the query a whole lot...
WITH
cteParse AS
(
SELECT MyGroup = REPLACE(SUBSTRING(obj_usr_num,1,CHARINDEX('-',obj_usr_num,CHARINDEX('-',obj_usr_num)+1)-1),'-',' ')
FROM dbo.SomeTable
)
SELECT MyGroup, [Count] = COUNT(*)
FROM cteParse
GROUP BY MyGroup
ORDER BY MyGroup
;
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row".
First step towards the paradigm shift of writing Set Based code:
"Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."
When writing schedules, keep the following in mind:
"If you want it real bad, that's the way you'll likely get it." |
|
|
|
Jeff Moden
Aged Yak Warrior
USA
644 Posts |
Posted - 01/12/2013 : 13:40:11
|
quote:
Originally posted by JerryHD1
With my limited knowledge, I can read that a bit easier than the other response. However, I am getting an Incorrect syntax near the Keyword REPLACE on line 1, so not sure if it will affect the other REPLACE keywords.
Jerry Haskins
Hi Jerry,
Take a look at how Visakh posted the test data in his post. If you post readily consumable data like that in the future, you'll get a whole lot less errors from people because they'll have something to test with.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row".
First step towards the paradigm shift of writing Set Based code:
"Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."
When writing schedules, keep the following in mind:
"If you want it real bad, that's the way you'll likely get it." |
|
|
|
JerryHD1
Starting Member
USA
21 Posts |
Posted - 01/12/2013 : 13:40:31
|
I'm simply copying and pasting your script, but needing to change @t to read #mytable. The #mytable is already set, the only difference being the actual numbers. I changed the numbers for security reasons, but still are set to xxxxxx-xx-x-xxxx. There could be additional numbers after the final 4 xxxx, but these do not matter, my query could actually stop with the first character of the final 4 xxxx. For instance, if the final 4 xxxx were 6000, it tells me it is a 6 series 4 character number. So only the 6 is important, not the remaining 3 xxx characters. That being said, I can not look at the 4 xxxx as being final characters, but simply the next set after the single character. Complicated I know. :)
Jerry Haskins |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47952 Posts |
Posted - 01/12/2013 : 13:43:47
|
whats the exact error message? also is this part of bigger batch of queries?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
JerryHD1
Starting Member
USA
21 Posts |
Posted - 01/12/2013 : 13:44:05
|
I agree Jeff, I am just trying to limit the data due to security concerns. Not really sure how to resolve that, but I tried by showing what my temp table looked like after filling it from my original select into statement. I simply changed the numbers and letters.
Jerry Haskins |
|
|
|
Jeff Moden
Aged Yak Warrior
USA
644 Posts |
Posted - 01/12/2013 : 13:45:55
|
So post some (fake) data in a readily consumable format with the things in the final 4 like what you're describing.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row".
First step towards the paradigm shift of writing Set Based code:
"Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."
When writing schedules, keep the following in mind:
"If you want it real bad, that's the way you'll likely get it." |
|
|
|
JerryHD1
Starting Member
USA
21 Posts |
Posted - 01/12/2013 : 14:01:08
|
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 |
|
|
|
JerryHD1
Starting Member
USA
21 Posts |
Posted - 01/12/2013 : 14:05:42
|
The actual error message is as follows.
Could not execute statement.
Incorrect syntax near the keyword 'REPLACE'
Sybase error code=156
Severity Level=15, State=2, Transaction State=1
Line 1
Jerry Haskins |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47952 Posts |
Posted - 01/12/2013 : 14:07:35
|
this will not work fine as you've values with 4 - characters. thats why i asked in beginning if its consistent
the above values will work fine with first suggestion
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
JerryHD1
Starting Member
USA
21 Posts |
Posted - 01/12/2013 : 14:17:42
|
I am sorry about that, when I stated consistant, I meant the first portion only. Always returns xxxxxx-xx-x-xxxx no matter what and the only data I need to look at really is xxxxxx-xx-x-x. I do not need to go further than 13 spaces. In all actuality, the numbers could go further out from originally discribed. My bad, sorry guys.
Jerry Haskins |
|
|
|
JerryHD1
Starting Member
USA
21 Posts |
Posted - 01/12/2013 : 14:40:57
|
visakh16
Your original solution should work fine. I am trying to tweek it a bit. Right now you list the First6 characters, then the Next2 Characters. I then need to ignore the next single character, but list the Next4 and then end with your count function.
Jerry Haskins |
|
|
|
Topic |
|
Basic Query Experience Only, Tricky Query Needed
