string search - SQL Server Forums

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

string search

Author

Topic

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-06-29 : 16:47:49

hi,

i have a column with varchar(50) values where i want to include all strings that might start with hypens, undescore, coma or might end with space, hypen, etc.

what would be the best select solution.
e.g.:


select
string_text from My_table
where
string_text like '[%-_.,%]searched_string[%-_.,%]'

pduffin
Yak Posting Veteran

68 Posts

Posted - 2010-06-29 : 16:52:46

What about using LEFT and RIGHT 1 to get the first and last char to check?

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-29 : 16:57:55

OK, let me see if I have this

You want to SELECT from a table, all rows where a columns is a funky char?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-06-29 : 17:03:49

i want to instead of checking for each string separately, i want to combine all rules in one.

e.g.:


select
string_text from My_table
where
--string_text like '[%-_.,%]searched_string[%-_.,%]'
string_text like 'searched_string,%'
or string_text like 'searched_string-%'
or string_text like 'searched_string %'

so instead of all three rules
i would like to combine and said:
string_text like 'searched_string[_-, ]'

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-29 : 17:15:44

[code]
CREATE TABLE #myTable99(Col1 varchar(8000))
GO

INSERT INTO #myTable99(Col1)
SELECT 'Four Score and seven years ago ' UNION ALL
SELECT '- out fore fathers set forth ' UNION ALL
SELECT '% We The People ' UNION ALL
SELECT 'in Order to form ' UNION ALL
SELECT '[a perfect UNION '
GO

CREATE TABLE #Search(s char(1))
GO

INSERT INTO #Search(s)
SELECT '[' UNION ALL
SELECT '%' UNION ALL
SELECT '-' UNION ALL
SELECT '_' UNION ALL
SELECT '.' UNION ALL
SELECT ',' UNION ALL
SELECT ']'
GO

SELECT * FROM #myTable99 a JOIN #Search b ON SUBSTRING(a.Col1,1,1) = b.s

DROP TABLE #myTable99, #Search
GO
[/code]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

pduffin
Yak Posting Veteran

68 Posts

Posted - 2010-06-29 : 18:14:03

select string_text
from My_table
where left(string_text,1) like '[_,-]' or
right(string_text,1) like '[_,-]'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources