Using the replace command - 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

Using the replace command

Author

Topic

meef
Posting Yak Master

113 Posts

Posted - 2010-08-11 : 13:39:17

Fairly simple question. I have a bunch of variations of "Walmart" that I want to group together. Most were able to be grouped by grouping on the field and doing a LEFT(7). There is one though that is "Wal-mart" that it didn't group on because of the hyphen.

I know the replace command could work, but I'm not sure how to do it. I don't want to use the literal string like this:

SELECT REPLACE('Wal-mart','-','');
GO

in case there are other companies I may have missed. Can I put the column name in there some how?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-11 : 13:42:14

you can use field name there

REPLACE(Fieldname,'-','')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

meef
Posting Yak Master

113 Posts

Posted - 2010-08-11 : 13:44:19

Thanks, I'll give it a shot.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-11 : 13:45:43

np...try it and let us know how you got on

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

meef
Posting Yak Master

113 Posts

Posted - 2010-08-11 : 13:54:25

Well, I tried adding to the code I had and it didn't work:

REPLACE(p.conm, '-', '') as [Consignee]

So I put this on a new line in the SELECT statement but I still get both Walmarts...

REPLACE('WAL-MART', '-', '')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-11 : 13:55:43

why it didnt work?
what did
SELECT REPLACE(p.conm, '-', '') ... give you?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

meef
Posting Yak Master

113 Posts

Posted - 2010-08-11 : 13:57:06

It just still showed me both variations of Walmart, the Wal-mart and Walmart. It's like I didn't put the code in there.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-11 : 14:02:58

quote:
Originally posted by meef

It just still showed me both variations of Walmart, the Wal-mart and Walmart. It's like I didn't put the code in there.

Can you show us the whole query that you ran.

Kristen
Test

22859 Posts

Posted - 2010-08-11 : 14:05:00

Perhaps you want to replace specifics, rather than a hyphen (which might exist elsewhere in the data)

REPLACE(p.conm, 'WAL-MART', 'WALMART') as [Consignee]

or, if there are several popluar variations:


REPLACE(REPLACE(REPLACE(
    p.conm
    , 'W-ALMART', 'WALMART')
    , 'WA-LMART', 'WALMART')
    , 'WAL-MART', 'WALMART')
          as [Consignee]

meef
Posting Yak Master

113 Posts

Posted - 2010-08-11 : 14:16:50

This was my select statement:

SELECT	MAX(@ClientName) as [Client_Name],
				SUM(p.fbttl) as [Total],
				REPLACE(p.conm, '-', '') as [Consignee],
				MAX(p.invno) as [Invoice_Number],
				p.invdt as [Invoiced],
				MAX('Parcel') as [Mode],
				substring(LTRIM(str(year(invdt))),3,2)+dbo.padl(ltrim(str(month(invdt))),'0',2) as [Per_Invoiced]
		FROM TBLParcelMain.dbo.ParcelMain p
		WHERE	p.ACCT = @ClientCode AND
				p.invdt >= @InvoicedLo AND
				p.invdt <= @InvoicedHi AND
				p.pc = 'P'
		GROUP BY p.conm, p.invdt
		UNION
		SELECT	MAX(@ClientName) as [Client_Name],
				SUM(f.i_fbttl) as [Total],
				REPLACE(f.conm, '-', '') as [Consignee],
				MAX(f.BTSInv) as [Invoice_Number],
				f.btsinvdt as [Invoiced],
				MAX('FP') as [Mode],
				substring(LTRIM(str(year(btsinvdt))),3,2)+dbo.padl(ltrim(str(month(btsinvdt))),'0',2) as [Per_Invoiced]
		FROM TBLMaster.dbo.FreightPayment f	
		WHERE	f.ACCT = @ClientCode AND
				f.btsinvdt >= @InvoicedLo AND
				f.btsinvdt <= @InvoicedHi AND
				f.pc = 'P'
		GROUP BY f.conm, f.btsinvdt

I'll try those in a bit Kristen, thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-11 : 14:19:26

shouldnt group by be also

GROUP BY REPLACE(p.conm, '-', '') , p.invdt

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-11 : 14:21:40

quote:
Originally posted by visakh16

shouldnt group by be also

GROUP BY REPLACE(p.conm, '-', '') , p.invdt

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Right..the GROUP BY wont work..But at least the output of SELECT should show 'WalMart' instead of 'Wal-Mart'...yes?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-11 : 14:29:09

yeah it should be. But OP sounded as if he wanted all of the similar to be regarded as one group thats why i think tweak to GROUP BY is needed

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources