INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Greetings, We have a MS Sql Server

Greetings, We have a MS Sql Server

(OP)
Greetings,
We have a MS Sql Server table which outputs data like this:
(1000 OR 1001 OR 1002 OR 1003 OR 1004) And Not (2000, 2001)

I need to report the data this way:
(Alfred OR Bruce OR Crystal OR David OR Esperanza) AND NOT (Monica, Ruben)

There is already a table with the user data. The column are UserID (int) and UserName (varchar(40)

Thank you kindly for any help you can provide me.

Best regards

RE: Greetings, We have a MS Sql Server

"table which outputs data" - your 'table' does not output 'data', your SELECT statement outputs data. Could you show us your SELECT statement that creates the output of
(1000 OR 1001 OR 1002 OR 1003 OR 1004) And Not (2000, 2001)

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Greetings, We have a MS Sql Server

(OP)
Hi Andy. Thank you very much for your quick response.
I know it does looks weird, but the example that I submitted reflects correctly how the data is stored in the column on the table. In reality, instead of the person's names I used in my example, the data corresponds to sales product codes which are used to produce some pricing calculations.
Once again. Thank you so much for looking at my posting.

RE: Greetings, We have a MS Sql Server

What Andy said remains true, even if you store data that way, only a query outputs data from a table. And that query would need a modification.

What can also be said is, that a query can't have (1000 OR 1001 OR 1002 OR 1003 OR 1004) And Not (2000, 2001) as part of it, as that can't be a part of a query, it's not a valid expression for a where clause.

As this is the data - as you say - and not a query, this doesn't need to be a valid expression, still all this doesn't help us to help you.

Bye, Olaf.

RE: Greetings, We have a MS Sql Server

Quote (Molkas)

the example that I submitted reflects correctly how the data is stored in the column on the table

Are you saying the data in one of the fields in your table looks something like this?

Field_X
(1000 OR 1001 OR 1002 OR 1003 OR 1004) And Not (2000, 2001) 
(123 OR 456 OR 345 OR 6223 OR 9098) And Not (2022, 2451) 
(1045 OR 1076 OR 1042 OR 1363 OR 1005) And Not (2763, 2521) 
(1072 OR 1067 OR 1042 OR 1703 OR 1054) And Not (2600, 2781) 
 

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Greetings, We have a MS Sql Server

To answer Andys question, you have to peek inside the tables data.

If you don't yet have Microsoft SQL Server Management Studio (shortly named SSMS) installed, the first step is doing that.
Secondly you need to be able to connect to the server hosting that database via SSMS.

Then you can do a simple query to look into the data:

CODE

SELECT * FROM usertable 

SSMS also offers further options to look at what you have in your database server, a list of databases and tables and views and other objects within the databases. That will be useful to find out how the database and table is named.

Since you know field names you might already be at that stage anyway, but you have to do a few more things before we could get into helping you with a query getting out what you want. Knowing a field name isn't enough.

Bye, Olaf.


RE: Greetings, We have a MS Sql Server

(OP)
Andy,
The answer is Yes to your last posting:
Quote
Are you saying the data in one of the fields in your table looks something like this?

Field_X
(1000 OR 1001 OR 1002 OR 1003 OR 1004) And Not (2000, 2001)
(123 OR 456 OR 345 OR 6223 OR 9098) And Not (2022, 2451)
(1045 OR 1076 OR 1042 OR 1363 OR 1005) And Not (2763, 2521)
(1072 OR 1067 OR 1042 OR 1703 OR 1054) And Not (2600, 2781)
Unquote

The data appears that way in one of the columns in the table. What I want to be able to do is to create a query which will replace this: (1000 OR 1001 OR 1002 OR 1003 OR 1004) And Not (2000, 2001 with this: (Alfred OR Bruce OR Crystal OR David OR Esperanza) AND NOT (Monica, Ruben)

I have seen some examples of XML functions but I have not been able to replace the ID (nvarchar) values with the actual UserNames while retaining the parenthesis as well as the logical operators (OR, And Not, etc.. I hope that explains a little better what I need.. Thank you

RE: Greetings, We have a MS Sql Server

Your last sentences hint on this not being actual values of actual fields, but computed values from a FOR XML query. Show that query.
Nothing is simpler than replacing the field name UserID with Username and get the text you want stuffed together with names instead of IDs.

Just by the way: These expressions still make no sense in themselves, (a or b or c) alone means (a or b or c), it excludes any other set of values in itself, there is no need for AND NOT (d or e).

Bye, Olaf.

RE: Greetings, We have a MS Sql Server

You may just simply create a UDF (user defined function) that accepts a parameter (a string), parses it, replaces the IDs with the names, and returns another string.

All of it would be based on a table like this (which I hope you already have)
ID    FName
1000  Alfred
1001  Bruce
1002  Crystal
1003  David
1004  Esperanza
2000  Monica
2001  Ruben
 

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Greetings, We have a MS Sql Server

(OP)
Hi again Andy,

Yes, there is already a table with those values (take a look at my initial request please:
Quote
Greetings,
We have a MS Sql Server table which outputs data like this:
(1000 OR 1001 OR 1002 OR 1003 OR 1004) And Not (2000, 2001)

I need to report the data this way:
(Alfred OR Bruce OR Crystal OR David OR Esperanza) AND NOT (Monica, Ruben)

There is already a table with the user data. The column are UserID (int) and UserName (varchar(40)

Thank you kindly for any help you can provide me.

Best regards
Unquote

Could you provide an example of the UDF I can use to get the results? btw. I do not have permissions to create/modify objects in SSMS. I would have to ask a user with enough privileges in SQL to do the testing for me.
Thank you

RE: Greetings, We have a MS Sql Server

> would have to ask a user with enough privileges in SQL to do the testing for me
That should rather be the person doing this form work, too. It is very tedious to talk to someone forwarding this to another and coming back with feedback even later.

That said, since Andy can solve the technical problem easily, given enough information, I'll leave the thread.

Just one more technical recommendation. A query you have already might only need to JOIN the users table to have that lookup. For example you have something STUFFing together tableX.userid in between the ORs and brackets and such, then that tableX.userid would need to be changed to users.username instead, that can be done once you add a JOIN users ON tableX.userid=users.userid in the FROM/JOINs part of the query.

To be very clear, it is much easier to clink into the step putting together these expressions, than parsing text for numbers to replace with names. A problem always should be solved on the stage it's easy to solve. That stage is where the user IDs are single informations easily replaced with the users name, not when you have a lengthy string.

Bye, Olaf.

RE: Greetings, We have a MS Sql Server

(OP)
Thank you Olaf

RE: Greetings, We have a MS Sql Server

(OP)
Thank you all for your assistance. I'm going to add a new post. Hopefully, It will explain better what I need.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close