• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 433
  • Last Modified:

Stripping out characters after a point

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
SMACD
Asked:
SMACD
1 Solution
 
rushShahCommented:
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
 
grossacCommented:
Can you give a better example of what the before and after will look like, it's not totally clear.
0
 
SMACDAuthor Commented:
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
royhsiaoCommented:
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
 
reb73Commented:
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
 
SMACDAuthor Commented:
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
 
reb73Commented:
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
 
SMACDAuthor Commented:
Excellent
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now