Extract text from numbers

  • 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?

  • 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

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • 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
  • 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.

  • 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.

  • 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/

  • 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
  • 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

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • 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.

  • 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

  • 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 πŸ™

  • 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

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • 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?

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

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

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

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