For getting substring .. - 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)

For getting substring ..

Author

Topic

pavan2143
Starting Member

2 Posts

Posted - 2008-05-26 : 00:49:18

Hi friends,

please help me out for finding substring of a string.

Examples:
'Life12345 - Life Insurance' to 'Life12345'
'Life123- gen Life' to 'Life123'

i need to remove all characters after space or '-'.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-05-26 : 01:05:26

Can try with charindex function to get position of space or - then use substring to get result.

nr
SQLTeam MVY

12543 Posts

Posted - 2008-05-26 : 01:05:46

left(@str, charindex(' ',@str)-1)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-05-26 : 02:16:54

hi pavan
pavan pls try this

create table #tb(CNT varchar(250))
INSERT #tb
Select 'Life12345 - Life Insurance'
union all Select 'Life123- gen Life'

select case
when (b.sp > b.un)
then
SUBSTRING(b.CNT,1,b.UN-1)

ELSE

SUBSTRING(b.CNT,1,b.SP-1)
end
from
(SELECT charindex(' ',cnt,1) AS SP, charindex('-',cnt,1) AS UN,cnt from #tb)B

ok tanx...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-26 : 02:27:44

[code]SELECT CASE WHEN CHARINDEX(' ',REPLACE(stringfield,'-',' '))>0
THEN LEFT(stringfield,CHARINDEX(' ',REPLACE(stringfield,'-',' '))-1)
ELSE stringfield
END FROM YourTable[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources