Trying to keep it all in SQL - SQL Server Forums

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

Trying to keep it all in SQL

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-13 : 12:35:31

Data conversion - as usual

So i'm getting better at avoiding third party apps to get from point A to point B in my data conversions. In one conversion i'm working on currently, i have only ONE step that i'm relying on pushing the resultset of some selects out to Excel and then concatenating an UPDATE/SET script in Excel, copying that back to sql and executing. I would like to remove that step and get this step done all in sql. Is there any way such can be done?
Basically, i'm identifying the number of spaces in a string which contains a person's first name and middlename in the same string (Firstname column). I'm pulling the Firstname and ECSOID (the unique identifier for that person) from my table with three different scripts to find entries with three different possible combinations of spaces in that cell:

--Finds entries with only one space in the string:
SELECT LEN(FirstName)- LEN(REPLACE(FirstName,' ',''))AS [Spaces], FirstName, ECSOID
FROM mni
WHERE LEN(FirstName)- LEN(REPLACE(FirstName,' ',''))= 1

--Finds entries with two spaces in the string:
SELECT LEN(FirstName)- LEN(REPLACE(FirstName,' ',''))AS [Spaces], FirstName, ECSOID
FROM mni
WHERE LEN(FirstName)- LEN(REPLACE(FirstName,' ',''))= 2

--Finds entries with three spaces in the string:
SELECT LEN(FirstName)- LEN(REPLACE(FirstName,' ',''))AS [Spaces], FirstName, ECSOID
FROM mni
WHERE LEN(FirstName)- LEN(REPLACE(FirstName,' ',''))= 3

I save each result set as a csv, bring it in to excel, delimit the Firstname column according to spaces. For the result set with one space, i concat (in excel) an update/set string that turns out to be 'Update MNI set FNAME='John' and Mname='James' where ECSOID='DCSO00123456'

For the resultset with two spaces, the concat becomes 'Update MNI set Fname='Karey" and Mname='Ann Marie' where ECSOID='DCSO00987654'

For the resultset with three spaces, the concat becomes 'Update MNI set fname ='Juan' and Mname='de los lagos' where ECSOID='DCSO00985675'

While it "works" to dump this out to excel and copy the script back to sql as a seperate script, it's eats up time in this process. For our data conversions, there is a time limit for the actual conversion. We have to kick the client offline, convert the data, and get them back online ASAP. Since they are law enforcement agencies, being offline for more than a few minutes is a bad thing.

At first glance, is there anyway you can see to keep excel out of this process?

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-13 : 15:00:18

why not implement it by means of temporary table where you split up name parts using above logic and again use that table back for your final update?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources