Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1191
  • Last Modified:

sql server string manipulation in stored procedure

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
suryajandhyala
Asked:
suryajandhyala
1 Solution
 
Lori99Commented:
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
 
ibostCommented:
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
 
Anthony PerkinsCommented:
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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now