how to split the text in sql using stored proceduer

Question

4.00/5 (1 vote)

See more:

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

SQL

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

Santhosh23

Updated 19-Oct-12 2:43am

v2

Add a Solution

4 solutions

Solution 1

Your query of split should be modified as follows

SQL

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

Posted 19-Oct-12 2:56am

SIVAMANI SQL

Solution 2

try below t-sql code :

SQL

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

Posted 19-Oct-12 2:57am

Bhushan Shah1988

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

SQL

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

Posted 19-Oct-12 2:59am

damodara naidu betha

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

MT_ · Accepted Answer · 2012-10-19T02:57:00

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.

SQL

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

Posted 19-Oct-12 2:57am

MT_

Comments

Santhosh23 19-Oct-12 9:04am

thanq u so much... its wrking fine

MT_ 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