| Author |
 Topic  |
|
|
Madhav
Starting Member
USA
38 Posts |
 Posted - 04/03/2009 : 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
India
22461 Posts |
Posted - 04/03/2009 : 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
USA
38 Posts |
Posted - 04/03/2009 : 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 - 04/03/2009 : 04:58:28
|
Use the CharIndex() and LEFT() or RIGHT() builtin method with the Substring() function
Regards
Thiyagarajan |
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 04/03/2009 : 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
Flowing Fount of Yak Knowledge
India
1693 Posts |
Posted - 04/03/2009 : 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 |
Edited by - bklr on 04/03/2009 06:17:57 |
|
|
|
Madhav
Starting Member
USA
38 Posts |
Posted - 04/03/2009 : 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
India
22461 Posts |
Posted - 04/03/2009 : 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 |
|
|
| |
Topic |
|
|
|
Need help on how to use Substring function...
