?
Solved

sql server string manipulation in stored procedure

Posted on 2004-08-02
3
Medium Priority
?
1,177 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 2000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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 backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

752 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