charindex,instr - 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 2000 Forums

Transact-SQL (2000)

charindex,instr

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2006-10-26 : 11:04:07

Hello,

I have a sql server 2005 database with a table that has a field called MedNames. The field has values such as:
sodium 6% med
DEXTROSE 5% 150 ML

I need to return the values before the % including the %. How can i do that?
Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-26 : 11:08:04

Select Substring(1,charindex('%',Mednames,1)) from table

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-26 : 11:09:56

[code]declare @test table (medname varchar(100))

insert @test
select 'sodium 6% med' union all
select 'DEXTROSE 5% 150 ML'

select left(medname, charindex('%', medname)) from @test[/code]

Peter Larsson
Helsingborg, Sweden

samuelclay
Yak Posting Veteran

71 Posts

Posted - 2006-10-26 : 12:42:27

interesting... I read it to mean the : 6% and 5%...

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-10-26 : 14:18:26

You should redesign your model so you don't violate Codd's Information Rule

Jay White

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-26 : 21:19:20

http://www.datamodel.org/NormalizationRules.html

Madhivanan

Failing to plan is Planning to fail