locked
Query help RRS feed

  • Question

  • User-2123122692 posted

    Hello,

    Here is my dilemma and I hope someone can help me:

    Table1   
    First                Last               Dept
    Dan               Smith            Accounting
    Joe                 Doe             Admin
    Mary               Boe             Accounting


    Table2   
    Contact
    068097110032083109105116104        (for ref. this is Dan Smith)
    074111101032068111101                   (for ref. this is Joe Doe)
    077097114121032066111101              (for ref. this is Mary Boe)


    I'm using the following function in my code to convert Table2 data to string

    private static string AsciiToString(string content)
        {
            string StrValue = "";

            if (content.Length < 3)
                return "";

            while (content.Length > 0)
            {
                // convert each ASCII value to the actual character
                StrValue += System.Convert.ToChar(System.Convert.ToInt32(content.Substring(0, 3))).ToString();
                // Remove the converted value
                content = content.Substring(3, content.Length - 3);


            }
            content = StrValue;

            return content;

        }


    What my question is, is there a way to convert the above function to a query.
    I'm trying to use the query below but it's not finding a match between the two tables because the data in one column is text and the other is not. Any idea how I can modify the query below to incorporate the AsciiToString() function? Thank you so much.

    SELECT Table2.Contact
    FROM Table2
    INNER JOIN
    (SELECT (First+' '+Last) AS FullName FROM Table1 WHERE dept='Accounting') Table1
    ON Table2.Contact = Table1.FullName
    ORDER BY Table2.Contact

    Tuesday, November 16, 2010 10:57 AM

Answers

  • User-1199946673 posted

    Any idea how I can modify the query below to incorporate the AsciiToString() function?
     

    You can't! I would add a column in table 1, and when inserting or updating the record, you add the string you create with your function, so yo can join the tables on this field. But I really don't see why you would store a name like this? It takes 3 times the amount of space, and you can't do anything with it..... 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 17, 2010 11:00 AM

All replies

  • User-2123122692 posted

    Could someone help please????

    Wednesday, November 17, 2010 8:16 AM
  • User-1199946673 posted

    Any idea how I can modify the query below to incorporate the AsciiToString() function?
     

    You can't! I would add a column in table 1, and when inserting or updating the record, you add the string you create with your function, so yo can join the tables on this field. But I really don't see why you would store a name like this? It takes 3 times the amount of space, and you can't do anything with it..... 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 17, 2010 11:00 AM