How do i ignore a character within a query

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

How do i ignore a character within a query

Author

Topic

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2010-09-07 : 09:28:09

Hello everyone.

I am querying a database with tons of addresses that i want to query. But the problem is, a lot of data has comma's within the addresses

for example. Flat 1, Edith Bell House

what syntax would i use to ignore the ',' when querying.
I dont want to delete, just ignore when querying.

Kind Regards

Rob

MCTS / MCITP certified

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-09-07 : 09:38:01

where replace(address,',','')='some address'

Madhivanan

Failing to plan is Planning to fail

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2010-09-07 : 09:39:38

Hello Madhivanan

what if i have lots of ',' within the query

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-09-07 : 09:48:14

quote:
Originally posted by masterdineen

Hello Madhivanan

what if i have lots of ',' within the query

It will take care

Madhivanan

Failing to plan is Planning to fail

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2010-09-07 : 09:54:57

Am i on the right lines with something like this

declare @address varchar(1)
set @address = ','
select * from FMW_Auto_2
where replace (@address,',','')

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-09-07 : 10:12:26

quote:
Originally posted by masterdineen

Am i on the right lines with something like this

declare @address varchar(1)
set @address = ','
select * from FMW_Auto_2
where replace (@address,',','')

declare @address varchar(1)
set @address = 'Flat 1'
select * from FMW_Auto_2
where replace (address,',','') like '%'+@address+'%'

Madhivanan

Failing to plan is Planning to fail

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2010-09-07 : 10:17:33

thank you..but

tried this and does not work. but

why are you setting @address = 'flat' when i want this to replace all of the ',' within the query result.

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2010-09-07 : 10:34:37

I am trying the following

select * from FMW_Auto_2
where replace (',',',','') and i receive the following error

Msg 4145, Level 15, State 1, Line 2
An expression of non-boolean type specified in a context where a condition is expected, near ')'

what would be a good example of expected condition

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-09-08 : 05:08:54

select address,replace(address,',','') as address_no_comma from FMW_Auto_2

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources