Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Basic Query Experience Only, Tricky Query Needed

Author  Topic 

JerryHD1
Starting Member

21 Posts

Posted - 2013-01-12 : 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

52326 Posts

Posted - 2013-01-12 : 12:31:56
[code]
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)
[/code]
EDIT: Fixed typo

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

JerryHD1
Starting Member

21 Posts

Posted - 2013-01-12 : 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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-12 : 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/

Go to Top of Page

JerryHD1
Starting Member

21 Posts

Posted - 2013-01-12 : 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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-12 : 13:04:51
is the format alway consistent? ie 4 parts separated by -?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

JerryHD1
Starting Member

21 Posts

Posted - 2013-01-12 : 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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-12 : 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/

Go to Top of Page

JerryHD1
Starting Member

21 Posts

Posted - 2013-01-12 : 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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-12 : 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/

Go to Top of Page

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2013-01-12 : 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."
Go to Top of Page

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2013-01-12 : 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."
Go to Top of Page

JerryHD1
Starting Member

21 Posts

Posted - 2013-01-12 : 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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-12 : 13:43:47
whats the exact error message? also is this part of bigger batch of queries?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

JerryHD1
Starting Member

21 Posts

Posted - 2013-01-12 : 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
Go to Top of Page

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2013-01-12 : 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."
Go to Top of Page

JerryHD1
Starting Member

21 Posts

Posted - 2013-01-12 : 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
Go to Top of Page

JerryHD1
Starting Member

21 Posts

Posted - 2013-01-12 : 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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-12 : 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/

Go to Top of Page

JerryHD1
Starting Member

21 Posts

Posted - 2013-01-12 : 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
Go to Top of Page

JerryHD1
Starting Member

21 Posts

Posted - 2013-01-12 : 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
Go to Top of Page
  Previous Page&nsp;  Next Page

- Advertisement -