How to access data from SQL Server S.P.M.A But i searched word is spma

Question

I have database value like S.P.M.A But I entered in text box is spma how to get value from database?

I used like operator but I did not get value

select * 
from tablename 
where name like '%spma%'

I used another method soundex

SELECT SOUNDEX ('spma'), SOUNDEX ('s.p.m.a')

uh, what about `SELECT * FROM tablename WHERE REPLACE(name, '.', '') LIKE '%spma%'`? (sql server [replace](https://msdn.microsoft.com/en-us/library/ms186862.aspx)) — Bagus Tesa, Dec 27 '16 at 06:51
But i have different combinations like s.p.m.a , s p m a, s. p. m. a — Rinto Antony, Dec 27 '16 at 08:19
lol, ask earlier.. [here, in this question at least it will help you on replacing the `REPLACE` clause](http://stackoverflow.com/questions/1007697/how-to-strip-all-non-alphabetic-characters-from-string-in-sql-server) -- notice `PATINDEX` and `STUFF`, it's a bit complicated as you actually had to iterate (almost) **each characters** within the string **in the database server**. yes, it'll introduce unnecessary overheads and such - but you're asking for it. Nevertheless, **you should update your question to reflect** on expected input, output, behavior -- your `s p m a`, `s. p. m. a.`. — Bagus Tesa, Dec 27 '16 at 10:42
oh right, before using `SOUNDEX` you ought to know how soundex actually works, [look at this screenshot](http://imgur.com/a/8wEL6), you'll notice something weird (but expected).. — Bagus Tesa, Dec 27 '16 at 10:48

score 2 · Answer 1 · answered Dec 27 '16 at 06:52

2

You could try comparing the name with the dots removed:

select *
from tablename
where replace(name, '.', '') like '%spma%'

answered Dec 27 '16 at 06:52

Tim Biegeleisen

1 Answers1