Solved

Stripping out characters after a point

Posted on 2011-03-07
8
417 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

776 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