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?
 
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
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
 
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:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.