Solved

Stripping out characters after a point

Posted on 2011-03-07
8
411 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
 
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now