| Author |
Topic |
|
shigman
Starting Member
14 Posts |
 Posted - 2009-06-25 : 09:00:52
|
Good Morning,
I have a description field that has SN and then a sequence of numbers.
I only want to display the information that is displayed before the SN and sequence.
How do I incorporate that in my query?
Example:
Currently shows like:
M85 CONVEYOR SN:6104059
WARRIOR 1800 SN:12301360
Desired Output
would show like:
M85 CONVEYOR
WARRIOR 1800
Thanks Sue |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-06-25 : 09:06:10
|
SELECT Col1,
SUBSTRING(Col1, 1, CHARINDEX(' SN:', Col1) - 1)
FROM Table
E 12°55'05.63"
N 56°04'39.26" |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-06-25 : 09:06:35
|
[code]select left(col, charindex('SN:', col) - 1)
from yourtable[/code]
KH
[spoiler]Time is always against us[/spoiler]
|
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-06-25 : 09:06:56
|
KH
[spoiler]Time is always against us[/spoiler]
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-06-25 : 09:08:01
|
And is case there is no SN: part
SELECT Col1,
SUBSTRING(Col1, 1, COALESCE(NULLIF(CHARINDEX(' SN:', Col1) - 1, -1), LEN(Col1)))
FROM Table
E 12°55'05.63"
N 56°04'39.26" |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-06-25 : 09:22:15
|
and this is my version, case there is no SN: part
SELECT LEFT(col, charindex('SN:', col + 'SN:') - 1)
FROM yourtable
KH
[spoiler]Time is always against us[/spoiler]
|
|
|
|
Kart
Starting Member
2 Posts |
Posted - 2009-06-25 : 09:48:24
|
will return both SN and non SN
SELECT CASE WHEN CHARINDEX(' SN:', Col1)=0 THEN Col1 ELSE LEFT(Col1,CHARINDEX(' SN:', Col1)) END FROM TableX
take life as its comes leaves as its goes |
|
|
|
shigman
Starting Member
14 Posts |
Posted - 2009-06-25 : 10:07:00
|
| Thanks. That works. I did find some fields that have a S/N also...can we incorporate the dropping of SN: and S/N: |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-06-25 : 10:10:45
|
[code]SELECT CASE
WHEN CHARINDEX(' SN:', Col1) > 0 THEN LEFT(Col1, CHARINDEX(' SN:', Col1) - 1)
WHEN CHARINDEX(' S/N ', Col1) > 0 THEN LEFT(Col1, CHARINDEX(' S/N ', Col1) - 1)
ELSE Col1
END
FROM Table1[/code]
E 12°55'05.63"
N 56°04'39.26" |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-06-25 : 10:10:47
|
[code]SELECT LEFT(replace(col, 'S/N', 'SN:'), charindex('SN:', replace(col, 'S/N', 'SN:') + 'SN:') - 1)
FROM yourtable
[/code]
KH
[spoiler]Time is always against us[/spoiler]
|
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-06-25 : 10:11:16
|
still slower
KH
[spoiler]Time is always against us[/spoiler]
|
|
|
|
shigman
Starting Member
14 Posts |
Posted - 2009-06-25 : 10:58:20
|
| got it. Thanks Guys |
|
|
|
tosscrosby
Aged Yak Warrior
676 Posts |
Posted - 2009-06-25 : 16:53:49
|
quote:
Originally posted by Peso
And is case there is no SN: part
SELECT Col1,
SUBSTRING(Col1, 1, COALESCE(NULLIF(CHARINDEX(' SN:', Col1) - 1, -1), LEN(Col1)))
FROM Table
E 12°55'05.63"
N 56°04'39.26"
quote:
Originally posted by khtan
and this is my version, case there is no SN: part
SELECT LEFT(col, charindex('SN:', col + 'SN:') - 1)
FROM yourtable
KH
[spoiler]Time is always against us[/spoiler]
Fight, fight, fight........
Edit: Seriously, the bottom line is receiving the best answer to resolve the issue. Just feeling a little happy today (must be the meds....or, lack of???)!
Terry
-- Procrastinate now! |
|
|
|
shigman
Starting Member
14 Posts |
Posted - 2009-06-30 : 09:06:40
|
Ok. So here is my end result (thanks to help from you guys!!!Thank you):
SELECT Parts_GL_TableF.Customer, LEFT(replace(b.itemdescription, 'S/N', 'SN:'), charindex('SN:', replace(b.itemdescription, 'S/N', 'SN:') + 'SN:') - 1) , Parts_GL_TableF.GL_Accnt, Parts_GL_TableF.Prod_Line, SUM(Parts_GL_TableF.Qty) , SUM(Parts_GL_TableF.Unit_Cost) , SUM(Parts_GL_TableF.Ext_Cost) , SUM(Parts_GL_TableF.Unit_Price) , SUM(Parts_GL_TableF.Ext_Amnt)
FROM Parts_GL_TableF LEFT OUTER JOIN (SELECT Customer, ItemDescription FROM ED.dbo.Equip_List_F GROUP BY Customer, ItemDescription) B ON Parts_GL_TableF.Customer = B.Customer
WHERE ((Parts_GL_TableF.Inv_Date>='01/01/2009' And Parts_GL_TableF.Inv_Date<=GetDate())) AND Parts_GL_TableF.Prod_Line IN ('ADPT', 'BEAR', 'BELT', 'BOLT', 'CHAN', 'CLTH', 'CPDS', 'CRBL', 'DRUM', 'EAGL', 'ELEP', 'ELPS', 'ENGP', 'ENPG', 'EXTE', 'FABR', 'FILT', 'FINL', 'GRID', 'HAZE', 'HOSE', 'HW', 'HYPG', 'HYPS', 'INER', 'KEY', 'MISC', 'NSHW', 'NSSC', 'NUTS', 'OBSO', 'OS', 'PINS', 'PULL', 'RADI', 'ROLL', 'SCRE', 'SEAL', 'SHAF', 'SHRP', 'SPRI', 'SPRO', 'STSC', 'STSH', 'TAPE', 'TESB', 'TRAC', 'TROM', 'TS', 'TSSC', 'USSC', 'VBEL', 'WASH', 'WHEL', 'WPPG', 'WPPR') AND Parts_GL_TableF.Customer NOT IN ('A C S')
GROUP BY Parts_GL_TableF.Prod_Line, Parts_GL_TableF.GL_Accnt, Parts_GL_TableF.Customer, b.ItemDescription
ORDER BY Parts_GL_TableF.Customer, b.ItemDescription, Parts_GL_TableF.Prod_Line
Now, my question is with my left outer join to match up Customers...Am I losing or dropping the Customers from Equip_List_F?
Sue |
|
|
|
shigman
Starting Member
14 Posts |
Posted - 2009-06-30 : 09:40:28
|
Ok. I changed the Left Outer Join to a Full outer join and that seemed to help....BUT I know that I have a list of Customers in the Equip_List_F table that have machines that are not in the Parts_GL_Table that I would like to list.
Any Ideas??? Help Please......
Sue |
|
|
|
|
|
|
How to drop data