SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SSIS and Import/Export (2012)
 Removing Text from a String
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

brubakerbr
Yak Posting Veteran

USA
53 Posts

Posted - 10/02/2013 :  11:43:04  Show Profile  Reply with Quote
I am currently updating an already existing SSIS package.
The current Package pulls data from an Excel Spread Sheet that is provided by our IT Department. It lists Machine Names of Computers and counts it for a License Report.

I currently have the Job (derived column) strip off the M (Mobile) or D (Desktop) from the first part of the machine name so that it returns just the user name, which is what I need for the report.

MBRUBAKERBR => BRUBAKERBR

However, our IT Department just implemented Windows 7 and with it a new Naming convention.
Now there is a 76A, B, C or D that is added to the end of all of the updated machines. If the machine has not been updated then it stays with the older Naming Convention (seen Above).

There are also machines that have to stay on XP, their names have been update to have X3A, B, C or D at the end of theirs.

MBRUBAKERBR76A or DBRUBAKERX3C

What I need is to remove the last part of the name so that I just get the user name out of it for reporting.

The issues is I can't use a LEFT, RIGHT, LTRIM or RTRIM expression as some of the computer names will only have the M or D in front (as they have not yet been upgraded).

What can I do to remove these characters without rebuilding this package?

Brian D. Brubaker
Business Intelligence Analyst
Viega LLC

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 10/02/2013 :  11:52:33  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
select right(name,len(name)-1) from
(
select
case
when right(name,1) in ('X3A','X3B','X3C','76A','76B','76C') then left(name,len_name)-3)
else name
end name
from tbl
) a


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30114 Posts

Posted - 10/02/2013 :  12:16:56  Show Profile  Visit SwePeso's Homepage  Reply with Quote
DECLARE	@Sample TABLE
	(
		Data VARCHAR(100) NOT NULL
	);
	
INSERT	@Sample
	(
		Data
	)
VALUES	('MBRUBAKERBR76A'),
	('DBRUBAKERX3C'),
	('MBRUBAKERBR'),
	('BRUBAKERBR');

WITH cteSource(Data, Offset, Cut)
AS (
	SELECT	Data,
		CASE
			WHEN Data LIKE '[MD]%' THEN 2
			ELSE 1
		END AS Offset,
		CASE
			WHEN Data LIKE '%X3[A-D]' THEN 4
			WHEN Data LIKE '%[0-9][0-9][A-D]' THEN 4
			ELSE 0
		END AS Cut
	FROM	@Sample
)
SELECT	Data,
	SUBSTRING(Data, Offset, LEN(Data) - Cut) AS SwePeso
FROM	cteSource



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

brubakerbr
Yak Posting Veteran

USA
53 Posts

Posted - 10/02/2013 :  13:16:47  Show Profile  Reply with Quote
But how would i add the script to my existing package. I have the Excel Source feeding into a series of Look-Ups to assign some specific values to them. I want to strip the unnecessary text and return only the user name fro the naming convention.

The Existing Data Conversion already strips the M and D from the front end so would I put this script into a Variable and then run the variable?

Brian D. Brubaker
Business Intelligence Analyst
Viega LLC
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 10/03/2013 :  03:52:28  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
I would import the data into a table then run the above to create another table or set a new column in that table - in that way you have a record of the source data and updated values.

If you want to do it in the data flow in the package then turn the above into an expression.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 10/04/2013 :  02:08:22  Show Profile  Reply with Quote
I would store the patterns in a table in sql server and you can use that to do a lookup with values from the file. if matched replace the matching part with blank ("") and you will get the patterns removed from your machine names

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

brubakerbr
Yak Posting Veteran

USA
53 Posts

Posted - 10/22/2013 :  11:23:14  Show Profile  Reply with Quote
@SwePeso: Have I told you how awesome you are lately.
Thank you again for the awesome script.

Trying to work out how to integrate it into my package now.

Thanks again.

Brian D. Brubaker
Business Intelligence Analyst
Viega LLC
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000