How to replace value in table column using replace function in SQL server

Hi

I have a path like below in one of the column " /ACCOUNT 9 Production Record/RHB Unit - Consumer" so I want this value when I select to be view as " /ACCOUNT 9 Production Record/Unit - Consumer"
so I try with following t-sql below
  select  [Path]=replace([Path],(right([Path],charindex('/',reverse([Path]),0)-1)),
      [Name]
	  from [PATHTEST]
  where Name like '%Account_I%'

Open in new window


anyhow this not work and return me with error below , any  suggestion how to correct  this syntax?

 
Msg 156, Level 15, State 1, Line 17
Incorrect syntax near the keyword 'from'.
motioneyeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nitin SontakkeDeveloperCommented:
Try as follows:

declare @path table
(
  [name] varchar(1000) not null
)

insert into @path ([name]) values (' /ACCOUNT 9 Production Record/RHB Unit - Consumer') -- /ACCOUNT 9 Production Record/Unit - Consumer

select substring([name], 1, charindex('/', [name], 5)) + substring([name], charindex('/', [name], 5) + 5, 100) [path]
  ,[Name]
from @path
where Name like '%Account%'

Open in new window


Not entirely sure what exactly you want to achieve. Should at least have few records as an example to experiment with. Also your WHERE clause doesn't the satisfy the condition to have the example record selected.
0
motioneyeAuthor Commented:
Hi Nitin,
Actually I just want our data which inside column path  which written as "/ACCOUNT 9 Production Record/RHB Unit - Consumer" to be  view
as "/ACCOUNT 9 Production Record/Unit - Consumer"
0
Nitin SontakkeDeveloperCommented:
So essentially, get rid of RHB, isn't it?

The script I provided does that.

The reason I ask is simple. Why not just get rid of the RHB (hard-coded) instead of all of this substring, right, left, business. Hope you understand what I mean.

You get the identical output with following:

select replace([name], 'RHB ', '') [path]
  ,[Name]
from @path
where Name like '%Account%'

Open in new window

0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Olaf DoschkeSoftware DeveloperCommented:
In your original query a closing  bracket is missing, or you have one opening bracket, too much. The FROM comes unexpected early, therefore this error message. Just count brackets.

select  [Path]=replace([Path],right([Path],charindex('/',reverse([Path]),0)-1)),
      [Name]
	  from [PATHTEST]
  where Name like '%Account_I%'

Open in new window


Or just take the RHB out, if it's only that, and get rid of unnecessarily complex expressions.

Bye, Olaf.
0
Mark WillsTopic AdvisorCommented:
*laughing* you can still use STUFF....  (from previous question)

Or in this case REPLACE([PATH],'RHB ','') will work for you

e.g.
declare @columnz varchar(100) = '/ACCOUNT 9 Production Record/RHB Unit - Consumer'

SELECT REPLACE(@COLUMNZ, 'RHB ','')
-- or
SELECT stuff(@COLUMNZ,charindex('RHB ',@COLUMNZ),4,'')

Open in new window

so in your code, i would change it to
select REPLACE([PATH], 'RHB ','') as [PATH] ,
      [Name]
	  from [PATHTEST]
  where Name like '%Account_I%' and charindex('RHB ',[PATH]) > 0

Open in new window

1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark WillsTopic AdvisorCommented:
Apart from mismatch brackets, the other factor in your code is REPLACE needs 3 parameters
select  [Path]=replace([Path],right([Path],charindex('/',reverse([Path]),0)-1),'')

-- but that would return 

/ACCOUNT 9 Production Record/

Open in new window

0
Scott PletcherSenior DBACommented:
select  [Path]=replace(replace([Path],(right([Path],charindex('/',reverse([Path]),0)-1)),
                   '/ACCOUNT 9 Production Record/RHB Unit - Consumer',
                   '/ACCOUNT 9 Production Record/Unit - Consumer')),
...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.