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.
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_numJD2313-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 1The 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.ThanksJerry 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 CntFROM TableGROUP 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 MVPhttp://visakhm.blogspot.com/ |
|
|
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 |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
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 |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
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 |
|
|
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 isSELECT 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 lastPartFROM table ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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 |
|
|
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 @tVALUES('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 lastPartFROM @toutput-----------------------------------------first6 Next2 IgnorePart lastPart-----------------------------------------JD2313 10 R 0002K33312 13 L 0001S22133 12 W 0004S22133 12 Z 0001QQQ123 11 Q 0004PPP123 12 M 0008PPP123 12 K 0010PPP123 12 L 0001PPP123 98 D 0001PPPXXX 07 L 0003 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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...WITHcteParse 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
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." |
|
|
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 |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
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 |
|
|
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." |
|
|
JerryHD1
Starting Member
21 Posts |
Posted - 2013-01-12 : 14:01:08
|
Select obj_usr_numinto #mytablefrom some_objwhere obj_usr_num between '2011-10-01 00:00:00.000' and '2013-10-01 00:00:00.000'Order by obj_usr_numSelect obj_usr_num from #mytableJD2313-10-R-0002-P00002K33312-13-L-0001 S22133-12-W-0004-P00001S22133-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 lastPartFROM #mytableIs 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
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=156Severity Level=15, State=2, Transaction State=1Line 1Jerry Haskins |
|
|
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 consistentthe above values will work fine with first suggestion------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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 |
|
|
JerryHD1
Starting Member
21 Posts |
Posted - 2013-01-12 : 14:40:57
|
visakh16Your 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 |
|
|
Previous Page&nsp;
Next Page
|
|
|
|
|