Query to update a word which has both Hypen & Spac

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

SQL Server Development (2000)

Query to update a word which has both Hypen & Spac

Author

Topic

krishnaveni.p
Starting Member

5 Posts

Posted - 2008-03-13 : 18:17:47

Hi,
I need a Query to Update a space with Hypen for the given word.
Eg : Test- Test
should be replaced as Test--Test. Space and Hypen can accour in any place . Regardless of the place , if the world has both space and hypen then that space should be replaced with hypen ..

will this search for me ?
select *
from TBL_NaturalSearch (nolock)
where keyword like '%-% %'
or keyword like '% %-%'

i need update statement to replace the same ...

It will be good if you suggest something on this ?

Thanks in Advance !!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-13 : 19:22:05

I think this does what you want:

DECLARE @Yak TABLE (Val VARCHAR(50))

INSERT @Yak
SELECT '12334456'
UNION ALL SELECT 'Test Test'
UNION ALL SELECT 'Test-Test'
UNION ALL SELECT 'Test -Test'
UNION ALL SELECT 'Test -Test- '
UNION ALL SELECT '- Test -Test -'
UNION ALL SELECT 'Test- Test'
UNION ALL SELECT 'Test Test-'
UNION ALL SELECT 'Test- Test- '

SELECT
	Val,
	REPLACE(REPLACE(Val, '- ', '--'), ' -', '--') AS Replaced
FROM
	@Yak
WHERE
	Val LIKE '%- %'
	OR VAL LIKE '% -%'

krishnaveni.p
Starting Member

5 Posts

Posted - 2008-03-13 : 19:27:34

Hi,
i too have formed a Query to satisfy .
can you please check and let me know ...

update TBL_NaturalSearch
set keyword = replace(keyword,' ','-')
where keyword in ( select keyword
from TBL_NaturalSearch where keyword like '%-% %' or keyword like '% %-%')

krishnaveni.p
Starting Member

5 Posts

Posted - 2008-03-13 : 19:34:28

Hi Lamprey,
Can you check the below Query and let me know .

update TBL_NaturalSearch
set keyword = replace(keyword,' ','-')
where keyword in ( select top 5 keyword
from TBL_NaturalSearch
where keyword like '%-% %' or keyword like '% %-%')

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-13 : 19:50:27

Well, a couple of things...

Your REPLACE will replace every space with a dash. Is that waht you want?

My previous question matters because of the way you are looking for dashes and spaces. The wildcard character (%) in the LIKE represents zero or more spaces so, if you are looking for " -" or "- " your LIKE will find them, but " foo-" and "-foo " will also satisfy that criteria.

So, if you only want to replace "- " and " -" with "--" then you should look at my code.

krishnaveni.p
Starting Member

5 Posts

Posted - 2008-03-13 : 20:14:24

Hi,
I need to replace all the spaces if the word has space and Hypen.
Basically i don't want a word which has both space and hypen .
I need a word only with hypen or only with a space .
So currently i am replacing all the words which has both space and hypen to hypen.

can you please let me know for the above criteria whether my Query will work .

rolandsantos
Starting Member

24 Posts

Posted - 2008-03-14 : 05:48:00

maybe my script will help you a little bit, it eliminates all words that both has hyphen and space.

declare @tmptab table (keyword varchar(50))

insert @tmptab
select 'Test-Test'
union all select 'Test -Test'
union all select 'Test- Test'
union all select 'Test - Test'
union all select 'Test - Test'
union all select 'Test - Test'

while exists(select keyword from @tmptab where keyword like '%- %' or keyword like '% -%')
begin
update @tmptab
set keyword = case when keyword like '% -%' then replace(keyword,' -', '-')
when keyword like '%- %' then replace(keyword,'- ', '-') end
where keyword like '%- %' or keyword like '% -%'
end

select * from @tmptab

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-14 : 12:12:01

quote:
Originally posted by krishnaveni.p

can you please let me know for the above criteria whether my Query will work .

It looks like it will work, but you want to be sure to test it before updating your database.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources