Removing Text from a String - 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

SQL Server 2012 Forums

SSIS and Import/Export (2012)

Removing Text from a String

Author

Topic

brubakerbr
Yak Posting Veteran

53 Posts

Posted - 2013-10-02 : 11:43:04

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
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2013-10-02 : 11:52:33

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.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-10-02 : 12:16:56

[code]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[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

brubakerbr
Yak Posting Veteran

53 Posts

Posted - 2013-10-02 : 13:16:47

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

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2013-10-03 : 03:52:28

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-04 : 02:08:22

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

brubakerbr
Yak Posting Veteran

53 Posts

Posted - 2013-10-22 : 11:23:14

@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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources