Solved

Stripping out characters after a point

Posted on 2011-03-07
8
421 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
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…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

729 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