SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Need help on how to use Substring function...
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Madhav
Starting Member

USA
38 Posts

Posted - 04/03/2009 :  03:16:46  Show Profile  Reply with Quote
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
22755 Posts

Posted - 04/03/2009 :  03:30:39  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
What is the maximum number of tilts that your string can have?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Madhav
Starting Member

USA
38 Posts

Posted - 04/03/2009 :  04:24:14  Show Profile  Reply with Quote
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.
Go to Top of Page

thiyagu_rind
Starting Member

46 Posts

Posted - 04/03/2009 :  04:58:28  Show Profile  Reply with Quote
Use the CharIndex() and LEFT() or RIGHT() builtin method with the Substring() function



Regards
Thiyagarajan
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 04/03/2009 :  05:21:29  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

bklr
Flowing Fount of Yak Knowledge

India
1693 Posts

Posted - 04/03/2009 :  06:17:03  Show Profile  Reply with Quote
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
Go to Top of Page

Madhav
Starting Member

USA
38 Posts

Posted - 04/03/2009 :  07:11:54  Show Profile  Reply with Quote
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.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 04/03/2009 :  08:33:32  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000