sql server string manipulation in stored procedure

Posted on 2004-08-02
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.
Question by:suryajandhyala
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

Expert Comment

ID: 11697488
See if this works for you:

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

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

Accepted Solution

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

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

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


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

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)

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

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…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

734 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