Query help

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

Answer 1

User-2123122692 posted

Could someone help please????

Answer 2

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