Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Stored Procedure String Manipulation

Author  Topic 

-wing-
Starting Member

12 Posts
Posted - 2006-11-02 : 18:32:47
I am somewhat new to the world of programming with SQL Server and was wondering if this could be done. Well I know it can be done but was wondering how it might be done.

I have a DTS package created to import a table from and AS400 server. What I need to do is take one field and parse that field into 5 different values for 5 new fields.

Here is what I know needs to be done but not sure how to put into the procedure.

CREATE proc ChangeHIS

as
	--Declare Variables
	Declare @LastName varchar,
		@FirstName varchar,
		@MI varchar,
		@ID varchar,
		@Dept varchar,
		@intCount int,
		@UserName varchar,
		@strTemp varchar

--Create Temporary Table

CREATE TABLE [EmployeeAudit].[dbo].[tmpTable] (
[UPUPRF] varchar (10), 
[UPTEXT] varchar (50)
)

select [UPUPRF], [UPTEXT] from tblHIS into tmpTable

GO


And something dealing with the below code as well.

@tmpString = RTRIM(LTRIM(@tmpString))

If charindex(@tmpString, ",") > 0 
	--'Manuel, Michael J - 78672 - SR MIS SUPPORT SPEC'    
	@LastName = Left(@tmpString, charindex(@tmpString, ","))
	@tmpString = RTRIM(LTRIM(Right(@tmpString, Len(@tmpString) - charindex(@tmpString, ",") + 1)))
	--'Michael J - 78672 - SR MIS SUPPORT SPEC'
	@FirstName = Left(@tmpString, charindex(@tmpString, " "))
	@tmpString = RTRIM(LTRIM(Right(@tmpString, Len(@tmpString) - charindex(@tmpString, " ") + 1)))
	If charindex(@tmpString, "-") > 1 
		--'J - 78672 - SR MIS SUPPORT SPEC'
		@MI = Left(@tmpString, 1)
		@tmpSting = RTRIM(LTRIM(Right(@tmpString, Len(@tmpString) - 2)
	End
	--'- 78672 - SR MIS SUPPORT SPEC'
	@ID = Left(@tmpString, charindex(@tmpString, " - "))
	@tmpString = RTRIM(LTRIM(Right(@tmpString, Len(@tmpString) - charindex(@tmpString, " - ") + 3)))
	--'SR MIS SUPPORT SPEC'
	@Dept = @tmpString
End


Hope someone can point me in the right direction

Page47
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2006-11-03 : 10:00:31
Here is a function I use to parse lists (I call them CSV's although the seperator doesn't have to be a comma).


create function [dbo].[parse_csv]
(
    @csv     varchar(8000),
    @sep     varchar(10)
) 
returns @Parsed table
(
    arrayid    int     identity(1,1),
    element    varchar(1000)
)
as
begin

insert into @Parsed(element)
select
    nullif(substring(@sep+@csv+@sep,n,charindex(@sep,@sep+@csv+@sep,n)-n),'') as element
from
    dbo.numbers
where
    n<=datalength(@sep+@csv+@sep) and
    n-datalength(@sep)>0 and
    substring(@sep+@csv+@sep,n-datalength(@sep),datalength(@sep))=@sep and
    charindex(@sep,@sep+@csv+@sep,n)-n>0
return
end

 
This function is dependant on a table ...

CREATE TABLE [dbo].[numbers](
	[n] [int] NOT NULL,
 CONSTRAINT [pk_numbers] PRIMARY KEY CLUSTERED 
(
	[n] ASC
)WITH FILLFACTOR = 100 ON [PRIMARY]
)

 
... that is filled with sequential positive integers from 0 to at least 8000.

You can use this function to parse into 3 pieces (using ' - ' as the @sep). Once you do that, you will need to apply business rules name, based on its multiple apparent configurations, to determine which pieces of the name you have.

Jay White
Go to Top of Page

Page47
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2006-11-03 : 10:04:52
Another way to get to the 3-piece mid step is to use the parsename function against your string.

declare @v varchar(100)
select @v = 'Manuel, Michael J - 78672 - SR MIS SUPPORT SPEC'
select
    parsename(replace(@v, ' - ', '.'), 3) as 'Name',
    parsename(replace(@v, ' - ', '.'), 2) as 'ID',
    parsename(replace(@v, ' - ', '.'), 1) as 'Dept'

 
Then again, apply your business rules to the name to break that down further.

You haven't given us the set of possible name configurations, so I can't help you there.

I recommend you try both solutions and see which one performs better (I suspect the latter).

Jay White
Go to Top of Page

Page47
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2006-11-03 : 10:06:12
I looks like there may be cases where you don't get all three seperators, so you'll need to account for that as well...


Jay White
Go to Top of Page

-wing-
Starting Member

12 Posts
Posted - 2006-11-04 : 12:05:47
First, I want to thank you guys for your responses. Maybe I didn't explain how big of a n00b I am at Stored Procedures. It also may have something to do with I have only been programming with VB or VB.NET for the last 10 or more years. Anytime I need something from a stored procedure, someone else did it for me. Now I am in a situation where I am the only person with any programming knowledge.

I apologize for not being clear, but here is what I have planned or would like to do. I am importing a query from an AS400. It goes right into a table (tblHIS). This is a scheduled job that kicks off a DTS Package. I want to setup another job that will parse out the UPTEXT field into the following fields - LastName, FirstName, MI, EmpID, JobTitle. The username is already in a field of its own.

I understand string manipulation quite a bit. And I just think the syntax of Stored Procedures is what's messing me up. From my code up above, I believe I have the two fields that are needed, now I just need to loop through the records and parse out the [UPTEXT] field.

Thank you for your responses again, and please don't feel afraid to tell me that I am complete n00b. :)
Go to Top of Page

-wing-
Starting Member

12 Posts
Posted - 2006-11-04 : 13:54:40
Well, I have done some research and have been working on this stored procedure. Just when I think I have it all fixed up, I get this error.

---------------------------
Microsoft SQL-DMO (ODBC SQLState: 42000)
---------------------------
Error 156: Incorrect syntax near the keyword 'SET'.
---------------------------
OK
---------------------------

I had quite a few before this one, but worked them out. - Here is the code as it currently stands.
CREATE proc ChangeHIS

as
	/*Declare Variables */
	Declare @LastName varchar,
		@FirstName varchar,
		@MI varchar,
		@EmpID varchar,
		@Dept varchar,
		@intCount int,
		@UserName varchar,
		@strTemp varchar,
		@iRow int

/*Create Temporary Table */

CREATE TABLE [EmployeeAudit].[dbo].[tblHISParsed] (
[USRPRF] varchar (10),
[LastName] varchar (50), 
[FirstName] varchar (50),
[MI] varchar (50),
[EmpID] varchar (50),
[JobTitle] varchar (50)
)

select [RowID], [USRPRF], [UPTEXT] from qryHIS

SET @intCount = @@ROWCOUNT /*Set the Max Counter */
SET @iRow = 1 /*Set the Incremented Counter */

WHILE @iRow <= @intCount
BEGIN

	/*Select one Record at a time */
	Select @strTemp = [UPTEXT], @UserName = [USRPRF] from qryHIS WHERE RowID = @iRow

	/*Parse the Name */
	If charindex(@strTemp, ",") > 0 
		/*'Manuel, Michael J - 78672 - SR MIS SUPPORT SPEC' */    
		SET @LastName = Left(@strTemp, charindex(@strTemp, ","))
		SET @strTemp = RTRIM(LTRIM(Right(@strTemp, Len(@strTemp) - charindex(@strTemp, ",") + 1)))
		/*'Michael J - 78672 - SR MIS SUPPORT SPEC' */
		SET @FirstName = Left(@strTemp, charindex(@strTemp, " "))
		SET @strTemp = RTRIM(LTRIM(Right(@strTemp, Len(@strTemp) - charindex(@strTemp, " ") + 1)))
		If charindex(@strTemp, "-") > 1 
			/*'J - 78672 - SR MIS SUPPORT SPEC' */
			SET @MI = Left(@strTemp, 1)
			SET @tmpSting = RTRIM(LTRIM(Right(@strTemp, Len(@strTemp) - 2)
		/*End */
		/*'- 78672 - SR MIS SUPPORT SPEC' */
		SET @EmpID = Left(@strTemp, charindex(@strTemp, " - "))
		SET @strTemp = RTRIM(LTRIM(Right(@strTemp, Len(@strTemp) - charindex(@strTemp, " - ") + 3)))
		/*'SR MIS SUPPORT SPEC'*/
		SET @Dept = @strTemp
	/*End */

	/*Input the new record into the new table */
	INSERT INTO [EmployeeAudit].[dbo].[tblHISParsed] ([USRPRF], [LastName], [FirstName], [MI], [EmpID], [JobTitle]) values (@UserName, @LastName, @FirstName, @MI, @EmpID, @Dept)

	/*Clear out the variables */
	SET @userName = ''
	SET @LastName = ''
	SET @FirstName = ''
	SET @MI = ''
	SET @EmpID = ''
	SET @Dept = ''

End 

GO


Also, if you noticed that my a couple of my end statements are commented out, that's because I was receiving an error on those. Is there no 'End' statement for if statements in Stored Procedures? Just doesn't seem right to me.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2006-11-04 : 14:12:15
If you want to execute multiple statements based on an IF statement, you have to enclose them in a BEGIN/END block.

if condition
	begin
	statement 1...
	statement 2...
	statement 3...
	end




CODO ERGO SUM
Go to Top of Page

-wing-
Starting Member

12 Posts
Posted - 2006-11-04 : 14:59:37
I am also guessing that you can not have nested being/end statements. Correct?

*Edit* - Just found a site that showed me nested begin/end statements are allowed.
Go to Top of Page

-wing-
Starting Member

12 Posts
Posted - 2006-11-04 : 15:54:35
Well - I have narrowed down the problem (and made some other minor corrections) - I get
---------------------------
Microsoft SQL-DMO (ODBC SQLState: 42000)
---------------------------
Error 156: Incorrect syntax near the keyword 'End'.
---------------------------
OK
---------------------------
on the Second If Statement in the following code. If I pull out the If Statement, I don't get any errors.

CREATE proc ChangeHIS

as
/*Declare Variables */
Declare @LastName varchar,
@FirstName varchar,
@MI varchar,
@EmpID varchar,
@Dept varchar,
@intCount int,
@UserName varchar,
@strTemp varchar,
@iRow int

/*Create Temporary Table */

CREATE TABLE [EmployeeAudit].[dbo].[tblHISParsed] (
[USRPRF] varchar (10),
[LastName] varchar (50),
[FirstName] varchar (50),
[MI] varchar (50),
[EmpID] varchar (50),
[JobTitle] varchar (50)
)

select [RowID], [USRPRF], [UsrDesc] from qryHIS

SET @intCount = @@ROWCOUNT /*Set the Max Counter */
SET @iRow = 1 /*Set the Incremented Counter */

WHILE @iRow <= @intCount
BEGIN

/*Select one Record at a time */
Select @strTemp = [UsrDesc], @UserName = [USRPRF] from qryHIS WHERE RowID = @iRow

/*Parse the Name */
If charindex(@strTemp, ",") > 0
Begin
/*'Manuel, Michael J - 78672 - SR MIS SUPPORT SPEC' */
SET @LastName = Left(@strTemp, charindex(@strTemp, ","))
SET @strTemp = RTRIM(LTRIM(Right(@strTemp, Len(@strTemp) - charindex(@strTemp, ",") + 1)))
/*'Michael J - 78672 - SR MIS SUPPORT SPEC' */
SET @FirstName = Left(@strTemp, charindex(@strTemp, " "))
SET @strTemp = RTRIM(LTRIM(Right(@strTemp, Len(@strTemp) - charindex(@strTemp, " ") + 1)))

If charindex(@strTemp, "-") > 1
Begin
/*'J - 78672 - SR MIS SUPPORT SPEC' */
SET @MI = Left(@strTemp, 1)
SET @tmpSting = RTRIM(LTRIM(Right(@strTemp, Len(@strTemp) - 2)
End /*Error Shows Up Here */

/*'- 78672 - SR MIS SUPPORT SPEC' */
SET @EmpID = Left(@strTemp, charindex(@strTemp, " - "))
SET @strTemp = RTRIM(LTRIM(Right(@strTemp, Len(@strTemp) - charindex(@strTemp, " - ") + 3)))
/*'SR MIS SUPPORT SPEC'*/
SET @Dept = @strTemp
End

/*Input the new record into the new table */
INSERT INTO [EmployeeAudit].[dbo].[tblHISParsed] ([USRPRF], [LastName], [FirstName], [MI], [EmpID], [JobTitle]) values (@UserName, @LastName, @FirstName, @MI, @EmpID, @Dept)

SET @iRow = @iRow + 1

/*Clear out the variables */
SET @userName = ''
SET @LastName = ''
SET @FirstName = ''
SET @MI = ''
SET @EmpID = ''
SET @Dept = ''

End

GO
Go to Top of Page

pootle_flump

1064 Posts
Posted - 2006-11-05 : 08:36:13
[code]SET @tmpSting = RTRIM(LTRIM(Right(@strTemp, Len(@strTemp) - 2)[/code]Not enough closing parentheses.
Go to Top of Page

-wing-
Starting Member

12 Posts
Posted - 2006-11-07 : 12:33:22
Ok - that was an idiot mistake. The syntax goes through but now I am getting an Error 207 Invalid column name ','. For each charindex that I use. Am I using this in an incorrect format?

Everything I know I learned from the web.
Go to Top of Page

-wing-
Starting Member

12 Posts
Posted - 2006-11-08 : 10:58:44
Found something else out - using charindex is a little backwards from the VB InStr Function. The instr function is InStr(StringToSearch, [WhatToSearchFor]) and charindex is CharIndex([WhatToSearchFor, StringToSearch)

but I am still getting a 207 error. I tried to use the following test code:

CREATE proc thisTemp

as

	Declare @LastName varchar,
		@FullName varchar,
		@pos int

Set @FullName = 'Michael Manuel'
Set @pos = charindex(" ", @FullName)

And get the following error:
Server: Msg 207, Level 16, State 3, Procedure thisTemp, Line 10
Invalid column name ' '.

I hope someone can help me out.

Everything I know I learned from the web.
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2006-11-08 : 12:55:10
using double quote ("") is a way sql server determines column names. You need single quoutes ('')



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

-wing-
Starting Member

12 Posts
Posted - 2006-11-08 : 14:46:55


That did the trick. Now to work out some other issues. Thank you so much guys and I am sure I will be back with more questions.

Everything I know I learned from the web.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site