Another replace ? - 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

General SQL Server Forums

New to SQL Server Programming

Another replace ?

Author

Topic

ConradK
Posting Yak Master

140 Posts

Posted - 2009-11-17 : 16:34:09

okay, so I'm not exactly even sure how to word it, otherwise I would be able to Google it.

I am using the replace function to take out all 'ar ' because I need references to Assault Rifles taken from the database. The problem is, that some things are named stAR or populAR etc. etc., and its removing the words! I have an idea to pull out those kinds of things by finding "anyletter"ar, replacing it to ****"anyletter"ar*** and then converting the **** out of it at the end of the function.

So my question is, is there a way to tell sql to look for "anyletter" as a value?

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-17 : 16:42:33

So you do know the following?

'AR' <> 'Assualt Rifle'

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

ConradK
Posting Yak Master

140 Posts

Posted - 2009-11-17 : 16:45:52

We are doing Amazon stuff, and they are rather strict with their punishments. I don't want to give them a reason to shut us down. AR does MEAN assault rifle, as we call manythings ARs, as we also call things AKs.

So... any help with my actual question would be appreciated. ;-P

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-11-17 : 16:53:52

Could you give some examples of what do you want removed and what you don't?

Jim

Everyday I learn something that somebody else already knew

ConradK
Posting Yak Master

140 Posts

Posted - 2009-11-17 : 16:56:54

So I guess the code would look like this

REPLACE(REPLACE(string, '*ANYLETER*ar', 'xxx*SAMEANYLETTER*arxxx'), 'xxx', '')

maybe i'm looking for some way to concatenate instead of a replace first, and then use the replace. Either way, I would need to know the "ANYLETTER" function...

ConradK
Posting Yak Master

140 Posts

Posted - 2009-11-17 : 16:58:54

So, direct examples are this

AR Style Rifle Part!!!
Guitar strings!!!
AR Cushion!
NcStar Awsome product!!!

We have thousands of these. I want to take out the 'ar ', but then it hits NcStar or Guitar or whatever.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-11-17 : 17:14:52

maybe something like

SELECT yourColumn,REPLACE(yourColumn,'AR ','')
FROM yourTable
WHERE yourColumn like 'AR%Rifle%'

Jim

Everyday I learn something that somebody else already knew

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-17 : 17:15:14

select ltrim(replace(' '+'AR Style Rifle Part!!!'+' ',' ar ',' XX ')) as newValue,'AR Style Rifle Part!!!' as oldValue union all
select ltrim(replace(' '+'Guitar strings!!!'+' ',' ar ',' XX ')),'Guitar strings!!!' union all
select ltrim(replace(' '+'AR Cushion!'+' ',' ar ',' XX ')),'AR Cushion!'union all
select ltrim(replace(' '+'NcStar Awsome product!!!'+' ',' ar ',' XX ')),'NcStar Awsome product!!!' union all
select ltrim(replace(' '+'NcStar Awsome AR product!!!'+' ',' ar ',' XX ')),'NcStar Awsome AR product!!!' union all
select ltrim(replace(' '+'NcStar Awsome product!!! AR'+' ',' ar ',' XX ')),'NcStar Awsome product!!! AR'

No, you're never too old to Yak'n'Roll if you're too young to die.

ConradK
Posting Yak Master

140 Posts

Posted - 2009-11-17 : 17:22:18

jimf; yeah i could do that, if the only combination was 'AR Rifle', but its not.

The thing is, we have soooo many products, and I need a way to write the code such that it can apply to all products in the listing, and ones that will come. If there is know way to say "anycharacter", then I'll have to think of some crazy, crazy work around.

I mean, is that what your telling me? Like know there is like Char(10) and char(13) and whatever. Can you say something like char(any), and encompase all characters?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-11-17 : 17:34:51

I'm not following you on what you mean by "AnyCharacter". My query will return all those fields that start with 'AR ' as the first three characters, and have 'Rifle' somewhere in the string, which solves the problem presented in your sample data. I don't have a feeling for what else you're looking for. The better sample data you can provide, with the result that you'd like to see, the better we can help you.

Jim

Everyday I learn something that somebody else already knew

ConradK
Posting Yak Master

140 Posts

Posted - 2009-11-17 : 17:57:51

We don't always use the word 'rifle', and I can't compile a list of exceptions. There are too many products.

The code I am currently using is

"REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(pe.METATAG_Description, CHAR(10) + CHAR(13), ' '), CHAR(10), ' '), CHAR(13), ' '), 'w/', 'with '), 'yak', 'xxxxx'), 'oak', 'vvvvv'), 'eak', 'zzzzz'), 'ar15 ', '.223 '), 'ar-15', '.223 ' ), 'ar 15', '.223 '), 'm4', ''), 'm-4', ''), 'ak47', '7.62x39 '), 'ak 47', '7.62x39 '), 'ak-47', '7.62x39 '), 'assault', ''), 'm16', ''), 'm-16', ''), 'm 16', ''), '/', ''), ' or ', ''), ' ak ', ''), ' ar ', ''), ' ar15', '.233 '), ' , and', ''), ', ,', ''), ', , ,', ''), ', .', '.'), 'AK ', ''), 'AR ', ''), 'zzzzz', 'eak'), 'xxxxx', 'yak'), 'vvvvv', 'oak'), ' ', ' ') AS Description"

as you see, i was able to make a work around for ak stuff, because words that end in ak are few, but words that end in ar are many. I could just write what I call exemption safety statements like I did for the ak words, but that seems like the wrong way. If there was a better way, I'de prefer to do it that way.

I really apreciate your guys' help with this. I am very new to SQL, and have never really done much coding like this.

I'm not sure how much clear I can be. I said it in my last post, but its like know there is like Char(10) and char(13) and whatever. Can you say something like char(any), and encompase all characters?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-11-17 : 18:41:19

You could be a heck of a lot clearer as you still haven't provided a decent sample of what's in your database and what you need the results to be. Good Luck, though. Look into "like '%[A-Z]%' as well as PATINDEX and CHARINDEX for possible help.

Jim

Everyday I learn something that somebody else already knew

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-18 : 02:33:20

Have you read webfred's reply?

Madhivanan

Failing to plan is Planning to fail

ConradK
Posting Yak Master

140 Posts

Posted - 2009-11-18 : 10:00:52

cool. I'm really new to this guys, and have a little bit of tunnel vision. If it doesn't look like what I want the code to be, I don't see it!

I really appreciate your patience, and will try those. You guys have been very patient, and I appreciate it.

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-18 : 10:37:05

DO THIS

Create a table with 2 columns

In column 1 put the column you want replaced

In col2 put what you want it replaced with

The you can do an update join

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-18 : 11:28:43

a list of products should have a category designation.

Category
CategoryID int
Description varchar(32)

Product
ProductID int
CategoryID int --> FK to Category.CategoryID

Then you could easily filter by category. Any system that can't easily distinguish guitar strings from assault rifles is inherently flawed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources