Function to get Number from word

Question

Function to get Number from word

rash3554

SSCommitted

Points: 1984
More actions
June 13, 2016 at 10:33 am

#400269

Hi,
I was wondering if there is a function that can return Number for Spell out Number.
i.e. if "Four " is input, I need to get output as 4
if "Fourth" is input , I need 4th
if "Second" is input , I need 2nd
if "Two" is input , I need 2
I was thinking of creating a mapping table. I know it is tedious, not sure if anybody can suggest me a better solution.
Thanks
Rs

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183699 More actions · Answer 1

A mapping table would be the best option in SQL Server. A function would be an option in a procedural language such as C#.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Alan Burstein SSC Guru Points: 61136 More actions · Answer 2

If you go the mapping table route do a search for "cardinal ordinal number list" or "... chart"... You may get lucky as someone out there may have already done this. I did a quick google search and didn't find anything but it may be out there...

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Lowell SSC Guru Points: 323516 More actions · Answer 3

there's a few threads on converting the opposite of this request/; numbers to words

the logic is basically the same, a bunch of find and replaces, the links and code samples might give you some ideas.

As Alan is pointing out, the order of operations for the replace is extremely important on this.

http://www.sqlservercentral.com/Forums/Topic794134-149-2.aspx

is this just an exercise in feasibility, or do you really need to do this as a solution to a problem?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Luis Cazares SSC Guru Points: 183699 More actions · Answer 4

How are you validating your input? What happens if someone sends "Seocnd"? Would you expect "thirty-five" or "thirty five"?

Be careful as this might be a problem.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

rash3554 SSCommitted Points: 1984 More actions · Answer 5

Yes, I do really need a solution. This is not a student assignment but a company project.

Thx

rash3554 SSCommitted Points: 1984 More actions · Answer 6

Luis,

I haven't thought of spelling mistakes in the word like 'Secnod', but for 'thirty-five' I remove all punctuations, so that it's easier to apply rules.

Lowell SSC Guru Points: 323516 More actions · Answer 7

rash3554 (6/13/2016)
Yes, I do really need a solution. This is not a student assignment but a company project.
Thx

ok, so is this a find and replace in a paragraph, or are you re-constructing "twenty two million two hundred and fifty seven thousand eight hundred and fourteen dollars" into a number?

help us help you!

give us a CREATE TABLE

give us sample data via INSERT INTO

give us expected results

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

rash3554 SSCommitted Points: 1984 More actions · Answer 8

Here's the table structure,

Create table dbo.Homeaddr(HomeID INT,Address varchar(200));

INSERT INTO dbo.Homeaddr VALUES(1, 'Four Union Ave');

INSERT INTO dbo.Homeaddr VALUES(2,'Fifth Avenue ');

INSERT INTO dbo.Homeaddr VALUES(3,'Twenty First and Main Street ');

INSERT INTO dbo.Homeaddr VALUES(4,'Seventeenth Street');

INSERT INTO dbo.Homeaddr VALUES(5,'102 Twenty Nine Blvd');

This is the expected output

Create table dbo.OutputHomeaddr(HomeID INT,Address varchar(200));

INSERT INTO dbo.OutputHomeaddr VALUES(1, '4 Union Ave');

INSERT INTO dbo.OutputHomeaddr VALUES(2,'5th Avenue ');

INSERT INTO dbo.OutputHomeaddr VALUES(3,'21st and Main Street ');

INSERT INTO dbo.OutputHomeaddr VALUES(4,'17th Street');

INSERT INTO dbo.OutputHomeaddr VALUES(5,'102 Twenty Nine Blvd');

For HomeID=5, since address doesn't begin with a number, I do not have to do the replace.

Luis Cazares SSC Guru Points: 183699 More actions · Answer 9

I wonder what would happen if you find "23 hundred Someplace Rd". That's a real possibility if you have free text fields.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

rash3554 SSCommitted Points: 1984 More actions · Answer 10

rash3554

SSCommitted

Points: 1984

June 13, 2016 at 1:21 pm

#1884762

I am told by PM, we will not touch those cases.

Jeff Moden SSC Guru Points: 1004166 More actions · Answer 11

It will ultimately be a whole lot easier and a whole lot less expensive both upfront and over time if you simply purchased some "CASS Certification" software, which will do as you ask and a whole lot more.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Thomas Rushton SSC-Insane Points: 22649 More actions · Answer 12

Jeff Moden (6/13/2016)
It will ultimately be a whole lot easier and a whole lot less expensive both upfront and over time if you simply purchased some "CASS Certification" software, which will do as you ask and a whole lot more.

Particularly when you get to processing places such as "Sevenoaks" or "Seven Dials"...

Thomas Rushton
blog: https://thelonedba.wordpress.com

9423t8 Mr or Mrs. 500 Points: 507 More actions · Answer 13

Jeff Moden (6/13/2016)
CASS Certification software

You are assuming the OP is dealing only with USA addresses.

In the UK, the equivalent would be 'postcode software,' and I imagine other countries have their national equivalent as well.

As an aside, you haven't lived until you've had to cleanse an address list generated from info. entered by (usually drunk*) people on a website form; where the address could literally be in any country, and where the output has not been escaped properly at some point :rolleyes:, so characters like ä and ñ (for example) have all been "translated" into TWO non-accented characters each by the time they reach you as a list to cleanse. Now THAT was challenging.

(* the data was names/addresses of people wishing further information from a producer of an alcoholic beverage)