How to drop data - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

How to drop data

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources