Click here to Skip to main content
12,294,775 members (64,275 online)
Rate this:
 
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 2:41am
Edited 19-Oct-12 2: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 19-Oct-12 9:04am
   
thanq u so much... its wrking fine
Milind Thakkar 19-Oct-12 9:07am
   
Glad it helped. Please mark it as answer/upvote if it has solved the issue.
Santhosh23 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
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160525.2 | Last Updated 19 Oct 2012
Copyright © CodeProject, 1999-2016
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