Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL Server
hi all,
 
how to split the text in sql server using stored procedure like
@serchtext='city,sate'
how to split city ,sate separate my out put like
 
@city=city
@state=state
 
my query like this
ALTER PROCEDURE [dbo].[SP_TESTSEARCH](@Mode int,@INDUSTRY varchar(100),@JOB_TYPE varchar(100),@SERCHTEXT VARCHAR(100))
AS
BEGIN
 
Declare @city varchar(100);
Declare @sta varchar(100);
 
SET @city=SUBSTRING(@SERCHTEXT,0,CHARINDEX(@SERCHTEXT,',',0)-1)
 
Set @sta=SUBSTRING(@SERCHTEXT,(CHARINDEX(@SERCHTEXT,',',0)+1),(lEN(@SERCHTEXT)-(CHARINDEX(@SERCHTEXT,',',0)-1)))
but i got the error like 'Invalid length parameter passed to the LEFT or SUBSTRING function.'
 
how to write a query for split and set the values for declare variables
 
thanking you
Posted 19-Oct-12 3:41am
Edited 19-Oct-12 3:43am
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Your query of split should be modified as follows
 
SET @city=SUBSTRING(@SERCHTEXT,0,CHARINDEX(',',@SERCHTEXT,0))
 
Set @sta=SUBSTRING(@SERCHTEXT,(CHARINDEX(',',@SERCHTEXT,0)+1),(lEN(@SERCHTEXT)-(CHARINDEX(',',@SERCHTEXT,0)-1)))
 
since charIndex will take first input parameter as which string should be searched
 
Thanks
Regards
SIVAMANI
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

try below t-sql code :
 
declare @SERCHTEXT varchar(50)
set @SERCHTEXT = 'city,sate'
 
Declare @city varchar(100);
Declare @sta varchar(100);
 
SET @city=SUBSTRING(@SERCHTEXT,0,CHARINDEX(',',@SERCHTEXT,1))
 
Set @sta=SUBSTRING(@SERCHTEXT,(CHARINDEX(',',@SERCHTEXT,0)+1),(lEN(@SERCHTEXT)-(CHARINDEX(',',@SERCHTEXT,0)-1)))
 
select @city
select @sta
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

You have used CharIndex wrongly,
Two problem:
1. First parameter is the character you want to find and second is the string
2. You don't need to do -1
 
Use following modified query.
 
select SUBSTRING('city,sate',0,CHARINDEX(',','city,sate',0))
 
select SUBSTRING('city,sate',
(CHARINDEX(',','city,sate',0)+1),(lEN('city,sate')-(CHARINDEX(',','city,sate',0))))
 

Hope that helps. If it does, mark answer as solution and/or upvote.
 
Thanks
Milind
  Permalink  
Comments
Santhosh23 at 19-Oct-12 9:04am
   
thanq u so much... its wrking fine
Milind Thakkar at 19-Oct-12 9:07am
   
Glad it helped. Please mark it as answer/upvote if it has solved the issue.
Santhosh23 at 29-Jan-13 11:49am
   
hi Milind,
your query wroking fine.
 
select SUBSTRING('city,sate,county,nat',0,CHARINDEX(',','city,sate,county,nat',0))

select SUBSTRING('city,sate,county,nat',
(CHARINDEX(',','city,sate,county,nat',0)+1),(lEN('city,sate,county,nat')-(CHARINDEX(',','city,sate,county,nat',0))))
 
how to write the same query using 4 parameters please help me
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

Hi Santhosh,
 
You didn't place the parameters to the CHARINDEX function properly. You should use
CHARINDEX(',',@SERCHTEXT,0).
 
Try this code block
 
Declare @city varchar(100);
Declare @sta varchar(100);
 
Declare @SERCHTEXT varchar(200) ='City,State';
 
 
SET @city=SUBSTRING(@SERCHTEXT,0,CHARINDEX(',',@SERCHTEXT,0))
 
Set @sta=SUBSTRING(@SERCHTEXT,(CHARINDEX(',',@SERCHTEXT,0)+1),(lEN(@SERCHTEXT)-(CHARINDEX(',',@SERCHTEXT,0)-1)))
 
SELECT @City,@sta
 
Thank you
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 6,569
1 Sergey Alexandrovich Kryukov 6,168
2 DamithSL 5,228
3 Manas Bhardwaj 4,717
4 Maciej Los 4,150


Advertise | Privacy | Mobile
Web04 | 2.8.1411022.1 | Last Updated 19 Oct 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100