Greetings, We have a MS Sql Server

[Molkas]

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

 

[Andrzejek]

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
[tt] (1000 OR 1001 OR 1002 OR 1003 OR 1004) And Not (2000, 2001) [/tt]

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[Molkas]

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.

 

[Olaf Doschke]

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 [tt](1000 OR 1001 OR 1002 OR 1003 OR 1004) And Not (2000, 2001)[/tt] 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.

 

[Andrzejek]

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?
[pre]
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)
[/pre]

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[Olaf Doschke]

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:

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.

 

[Molkas]

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

 

[Olaf Doschke]

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.

 

[Andrzejek]

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)
[pre]
ID FName
1000 Alfred
1001 Bruce
1002 Crystal
1003 David
1004 Esperanza
2000 Monica
2001 Ruben
[/pre]


Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[Molkas]

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

 

[Olaf Doschke]

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

 

[Molkas]

Thank you Olaf

 

[Molkas]

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