Question regarding LIKE - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Question regarding LIKE

Author

Topic

raaj
Posting Yak Master

129 Posts

Posted - 2011-01-17 : 22:57:12

Hi Guys,
I am having a column named Company.
It has some sample values like this:
Company
Infosys
Tata
Microsoft 'Under Contract'
IBM 'Not Known'
GE,Samsung

Now I want to write a query that returns vlaues which are in single quotes. There are many rows.
I tried to write using LIKE Clause, but throwing error.

Any ideas???

sathishmangunuri
Starting Member

32 Posts

Posted - 2011-01-18 : 00:20:43

create table #t1(company varchar(40))
go
insert into #t1 values('Infosys')
insert into #t1 values('Tata')
insert into #t1 values('Microsoft ''Under Contract''')
insert into #t1 values('IBM ''Not Known''')
go
select substring(company,(CHARINDEX ('''', company , 1)+1),(CHARINDEX ('''', company , CHARINDEX ('''', company , 1)+1)-(CHARINDEX ('''', company , 1)))-1) from #t1
where company like '%''%''%'

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-01-18 : 00:23:53

you can try

select company,substring(company,CHARINDEX('''',company),LEN(company)) as [single quotes] from #temp
where company not like '%[^'']'

IRK

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-18 : 10:05:50

If sathishmangunuri's doesn't give what you needed, post expected result

Madhivanan

Failing to plan is Planning to fail

raaj
Posting Yak Master

129 Posts

Posted - 2011-01-20 : 22:11:58

Thanks sathish and Ranjit,
Both the solutions looks perfect.

Thanks,
Raaj.

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-01-21 : 03:52:13

You're welcome

IRK