Delete from ending based on number.

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 2005 Forums

Transact-SQL (2005)

Delete from ending based on number.

Author

Topic

Smajdi89
Starting Member

2 Posts

Posted - 2009-08-13 : 18:47:41

Hello SQL Teamers,
Hoping i could get some help. im trying to simplify one of the tables on our database, but since im still learning im scratching my head on how to do this.
I have a list of 30 thousand products, but i need to cut out all the numbers and such from the product name endings. Also if the number is attached to a word it will also delete that until the next space is shown. Here is a snippet of what im sort of looking for.

From This
lrg shirt 12-16
med shirt Ysl46
Times Magazine 10/2
Isi Whip Cream Chargers,24/box
Bdy Spry 8oz

Baby Wipes 48 Pc Dspl
King Tut Statue W/5"realist
suntan Lotion, Strawberry 6oz.
One Shots 5 Flvrs 1oz
Snackers Mini's W/bowl,216pcs.
Baby Pacifier Display,36pcs

To This:

lrg shirt
med shirt
Times Magazine
Isi Whip Cream
Bdy Spry

Baby Wipes
King Tut Statue
suntan Lotion, Strawberry
One Shots 5 Flvrs
Snackers Mini's
Baby Pacifier

Simple Version: Want it to delete starting from the right until it reaches an integer of some sort then it will stop deleting when it reaches a space.

Hope i can get some true SQL'rs to help me out.
Thanks!

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-14 : 00:50:42

supposed to leave the "5" in "One Shots 5 Flvrs"?

in that case it's going to take some manual intervention rather than a script that can sweep 'em all in one shot.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2009-08-14 : 17:22:46

update mytable
set Product = left(Product, len(Product) - CharIndex(' ', reverse(Product)))
where right(Product, 1) like '[0-9]'

You may want to consider the impact of trailing blanks in the original Product value.

=======================================
Men build too many walls and not enough bridges. -Isaac Newton, philosopher and mathematician (1642-1727)

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2009-08-14 : 17:26:37

Correction (Didn't interpret the requirements accurately)

update mytable
set Product = left(Product, len(Product) - CharIndex(' ', reverse(Product)))
where Product like '%[0-9]%'

HTH

=======================================
Men build too many walls and not enough bridges. -Isaac Newton, philosopher and mathematician (1642-1727)

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-14 : 21:00:17

doesn't produce desired result for "One Shots 5 Flvrs" but works for the others. OP should exclude that one from query

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources