Need help on how to use Substring function...

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)

Need help on how to use Substring function...

Author

Topic

Madhav
Starting Member

38 Posts

Posted - 2009-04-03 : 03:16:46

Hi,

I had a varchar value in sqlserver 2005,

str_value = 'str1~str2~str3~str4~str5'
I want the the substring upto 3rd tilt(~) character.
i.e 'str1~str2~str3'

How to do this sqlserver 2005?
Is there any built-in function in sqlserver?
Please help me.

Thanks in advance.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-03 : 03:30:39

What is the maximum number of tilts that your string can have?

Madhivanan

Failing to plan is Planning to fail

Madhav
Starting Member

38 Posts

Posted - 2009-04-03 : 04:24:14

quote:
Originally posted by madhivanan

What is the maximum number of tilts that your string can have?

Madhivanan

Failing to plan is Planning to fail

The max number of tilts will be 20. But in future it can exceed beyond this value.

thiyagu_rind
Starting Member

46 Posts

Posted - 2009-04-03 : 04:58:28

Use the CharIndex() and LEFT() or RIGHT() builtin method with the Substring() function

Regards
Thiyagarajan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-03 : 05:21:29

One method is


declare @s varchar(1000), @extract_string varchar(100),@i int
select @s= 'str1~str2~str3~str4~str5',@extract_string=''
set @i=1
while charindex('~',@s)>0 and @i<=3
begin
	select	@extract_string=@extract_string+left(@s,charindex('~',@s)),
			@s=substring(@s,charindex('~',@s)+1,len(@s)),
			@i=@i+1
end

print @extract_string

Madhivanan

Failing to plan is Planning to fail

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-03 : 06:17:03

try this too
DECLARE @str VARCHAR(32)
SELECT @str = 'str1~str2~str3~str4~str5'
SELECT SUBSTRING(@str,1,CHARINDEX('~',@str,CHARINDEX('~',@str,CHARINDEX('~',@str,1)+1)+1)-1)
SELECT LEFT(@str,CHARINDEX('~',@str,CHARINDEX('~',@str,CHARINDEX('~',@str,1)+1)+1)-1)

up to 3 strings only

Madhav
Starting Member

38 Posts

Posted - 2009-04-03 : 07:11:54

quote:

Originally posted by madhivanan

One method is


declare @s varchar(1000), @extract_string varchar(100),@i int
select @s= 'str1~str2~str3~str4~str5',@extract_string=''
set @i=1
while charindex('~',@s)>0 and @i<=3
begin
	select	@extract_string=@extract_string+left(@s,charindex('~',@s)),
			@s=substring(@s,charindex('~',@s)+1,len(@s)),
			@i=@i+1
end

print @extract_string

Madhivanan

Failing to plan is Planning to fail

This answered my question. Thanks very much.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-03 : 08:33:32

quote:
Originally posted by Madhav

quote:
Originally posted by madhivanan

One method is

declare @s varchar(1000), @extract_string varchar(100),@i int
select @s= 'str1~str2~str3~str4~str5',@extract_string=''
set @i=1
while charindex('~',@s)>0 and @i<=3
begin
select @extract_string=@extract_string+left(@s,charindex('~',@s)),
@s=substring(@s,charindex('~',@s)+1,len(@s)),
@i=@i+1
end

print @extract_string

Madhivanan

Failing to plan is Planning to fail

This answered my question. Thanks very much.

You are welcome

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources