Solved

Stripping out characters after a point

Posted on 2011-03-07
8
418 Views
Last Modified: 2012-05-11
Good day experts.

I have a column in my table that sort of contains a member number, although between the two systems I'm working on there are formatting issues.

In Database 1 I have values like XX23/34564 1 or XX33/54566C9
which correspond to Member numbers in Database 2 of 2334564 and 3354566.

What I want to be able to do is strip out everything after the numeric value after the / character.  I can then replace the non numeric values and get my member numbers.

How difficult would this be ?  Would I need a function to do this

Your assistance as always appreciated
0
Comment
Question by:SMACD
8 Comments
 
LVL 8

Expert Comment

by:rushShah
ID: 35058078
if your first two characters are fixed than you can do something like this,

select right(replace('XX23/34564','/',''), len(replace('XX23/34564','/',''))-2)
0
 
LVL 2

Expert Comment

by:grossac
ID: 35058096
Can you give a better example of what the before and after will look like, it's not totally clear.
0
 

Author Comment

by:SMACD
ID: 35058240
grossac

Before XX23/34564 1
After 2334564

or Before X32/435869C7
After 32435869

Rushshah
the first two charagters are not fixed, and the length of the field can vary.  but the member number is always seven or eight digits in length.  In the case of the XX23/34564 1 if I only took the numeric values I would have a member number of the right length but it would not match as the actual member number is 2334564 and not 23345641.

Hope this clarifies
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 6

Expert Comment

by:royhsiao
ID: 35058382
you could use the following function in excel
Put XX23/34564 in cell A1
Put the following in cell B1
=REPLACE(RIGHT(A1,LEN(A1)-2), 3, 1, "")
0
 
LVL 25

Expert Comment

by:reb73
ID: 35058421
Using regular expressions is an option (http://justgeeks.blogspot.com/2008/08/adding-regular-expressions-regex-to-sql.html), but a simple custom tsql function would be my best bet..

Here's a sample function -

create function dbo.fn_getnumberafterchar(@inputstr varchar(100), @char char(1))
returns varchar(100)
as
begin
	if @char in (NULL, '')
	begin
		set @char = '/'
	end
	declare @startpos int, @endpos int, @isnum bit, @retval varchar(100)
	select @startpos = isnull(charindex(@char,@inputstr), 0)
	if @startpos > 0
	begin
		set @isnum = 1
		set @endpos = @startpos
		while (@isnum = 1)
		begin
			set @endpos = @endpos + 1
			if not ascii(substring(@inputstr, @endpos,1)) between 48 and 57
			begin
				set @isnum = 0
				set @endpos = @endpos - 1
			end
		end
		select @retval = substring(@inputstr, 1, @endpos)
	end
	else
	begin
		select @retval = @inputstr
	end
	return @retval
end
go

/*
select dbo.fn_getnumberafterchar('XX33/54566C9', '/')
select dbo.fn_getnumberafterchar('XX23/34564 1', '/')
*/

Open in new window

0
 

Author Comment

by:SMACD
ID: 35058851
Reb 73

That's exactly what I want, however how do I call the function for every row in the table?

update #tmp
set membno = dbo.fn_getnumberafterchar(membno, '/')

or do I need to call it from a stored proc ?

Sorry never really used user functions

SMACD
0
 
LVL 25

Accepted Solution

by:
reb73 earned 500 total points
ID: 35059097
Yeah, it is a scalar function that has to be invoked for every row value..  

The syntax in your update statement is spot on, but it may take quite a while if the number of records to update is considerable.

Scalar UDF (User defined functions) are almost similar to the system inbuilt functions in terms of usage, you do have to prefix the function name with the schema (dbo in this case) though..
0
 

Author Closing Comment

by:SMACD
ID: 35067248
Excellent
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question