Solved

sql server string manipulation in stored procedure

Posted on 2004-08-02
3
1,173 Views
Last Modified: 2012-06-27
is there a way to unstring an input string delimited by a character into two string variables. i was able to do the same in cobol. basically users can type in a string with a '.' anywhere within a string. i need to extract the part of the string befrore the '.' and after the '.' into two seperate variables. i am coding this in a stored procedure on sql server.any suggestions.

your help is greatly appreciated.
0
Comment
Question by:suryajandhyala
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 7

Expert Comment

by:Lori99
ID: 11697488
See if this works for you:

create procedure SplitString
            @inputstring varchar(40),
            @outputStr1 varchar(40) OUTPUT,
            @outputStr2 varchar(40) OUTPUT
AS

SELECT @outputStr1 = substring(@inputstring,1,(charindex('.',@inputstring) - 1))
SELECT @outputStr2 = substring(@inputstring,(charindex('.',@inputstring) + 1),len(@inputstring))
0
 
LVL 10

Accepted Solution

by:
ibost earned 500 total points
ID: 11697577
You might add a check:

IF CHARINDEX('.', @inputstring) <> 0
BEGIN
   SELECT @outputStr1 = substring(@inputstring,1,(charindex('.',@inputstring) - 1))
   SELECT @outputStr2 = substring(@inputstring,(charindex('.',@inputstring) + 1),len(@inputstring))
END

Or maybe
IF CHARINDEX('.', @inputstring) = 0
BEGIN
   RAISERROR('No delimiter found in inputstring', 16, 1)
   RETURN -1
END

SELECT...
SELECT...


Otherwise you'll get a strange exception if the input string does not contain a delimiter character.

-Ian
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11697875
Try this:

Declare @Value varchar(200)

Set @Value = 'Before part of the string.After part of the string'

Select PARSENAME(@Value, 2), PARSENAME(@Value, 1)
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question