Extract text from numbers

Question

Extract text from numbers

compufreak

SSC Eights!

Points: 951
More actions
October 20, 2014 at 12:17 am

#291721

Hello everyone
I need some help with this one.
I have a field which contains something like prj(5616) .
I have been assigned to display the actual name and not the text with the number.
example: if prj(8616) is called Soccer , then I want display Soccer instead of prj(8616).
any advice?

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

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Do you have a table somewhere which includes both the code and the description?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Mohit Dhiman Say Hey Kid Points: 677 More actions · Answer 2

What decides prj(5616) is to be called soccer?

I mean i thought you wanted to extract prj from prj(5616), but this is something else you are talking about.

Anyways, you could use a lookup table to map what number is to be called what name, but again that depends on what you want to do here..

"The price of anything is the amount of life you exchange for it" - Henry David Thoreau

compufreak SSC Eights! Points: 951 More actions · Answer 3

Hi Phil

I looked at the two tables and only an ID Field is common between these two.

there seems to be no description field at all.

compufreak SSC Eights! Points: 951 More actions · Answer 4

Hi Mohit

yes i understand what you are saying but this is like really complicated as we pull data from all different tables joining it by a common ID.

kapil_kk SSC-Insane Points: 21316 More actions · Answer 5

Can you please provide table scripts and some sample data, that would be easier for us to understand the scenario

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Mohit Dhiman Say Hey Kid Points: 677 More actions · Answer 6

compufreak (10/20/2014)
Hi Mohit
yes i understand what you are saying but this is like really complicated as we pull data from all different tables joining it by a common ID.

Ok..but what i intended to ask was how do you decide what you want to call a certain Id (Or number) ?

Is there a business logic that defines that?

If so then one way is to put that in a lookup table and retrieve the name from this lookup table by joining it on Id..

"The price of anything is the amount of life you exchange for it" - Henry David Thoreau

Phil Parkin SSC Guru Points: 246960 More actions · Answer 7

compufreak (10/20/2014)
Hi Phil
I looked at the two tables and only an ID Field is common between these two.
there seems to be no description field at all.

Then where were you hoping to get the word 'soccer' from?

You need a table of Ids and descriptions to use as a lookup.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

compufreak SSC Eights! Points: 951 More actions · Answer 8

okay so i had a look at some other tables and the field is linked to another table.

so basically if the table contains prj(8345) then i am sure it must be linked to a project name .

any idea as to how i would be able to lookup this? and if i find it then show the name of the actual project instead of the numbers?

thanks for your help guys.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

compufreak (10/20/2014)
okay so i had a look at some other tables and the field is linked to another table.
so basically if the table contains prj(8345) then i am sure it must be linked to a project name .
any idea as to how i would be able to lookup this? and if i find it then show the name of the actual project instead of the numbers?
thanks for your help guys.

It's really easy - all you have to do is join the two tables on the common key - the columns which contain the value 'prj(8345)', and output the column containing 'soccer' in your select.

Try it. If it doesn't work, the post up your query along with sample data and scripts for all of the tables involved.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

compufreak SSC Eights! Points: 951 More actions · Answer 10

select distinct p.FormulaTypeID,p.FinancialYearID,p.VersionID,p.ProjectName,f.Formula

from tb_Projects p

inner join tb_FormulaTypes f on p.FormulaTypeID=p.FormulaTypeID

this is still not pulling the right results 🙁

Phil Parkin SSC Guru Points: 246960 More actions · Answer 11

compufreak (10/20/2014)
select distinct p.FormulaTypeID,p.FinancialYearID,p.VersionID,p.ProjectName,f.Formula
from tb_Projects p
inner join tb_FormulaTypes f on p.FormulaTypeID=p.FormulaTypeID
this is still not pulling the right results 🙁

We don't know your tables, your data, your business or your application architecture.

Now try to imagine how difficult it is to help you, given the scant information above.

Please provide some sample data and desired results, along the lines outlined in the link in my signature. We will have this sorted for you very quickly if you take the time to do that.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

compufreak SSC Eights! Points: 951 More actions · Answer 12

hello all

thanks for all your assistance yesterday.

i have a new requirement :

i would like to split this string after the "+" symbol:

prj(9204)(a)+prj(9205)(a)+prj(9206)(a)+prj(9207)(a)+prj(9208)(a)+prj(9209)(a)+prj(9210)(a)+prj(9211(a)

if that is my string, then i want the output to be like this :

prj(9204)(a)+

prj(9205)(a)+

prj(9206)(a)+

prj(9207)(a)+

prj(9208)(a)+

prj(9209)(a)+

prj(9210)(a)+

prj(9211(a)

any solutions?

Phil Parkin SSC Guru Points: 246960 More actions · Answer 13

Sure. Use Jeff Moden's Splitter – see here[/url].

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin