SQL function , loop and lookup example .

Don't loop!  

Instead, split the string into a table using an in-line table-valued function; most recommended is dbo.DelimitedSplit8K.

Then join to Dbo.CL_ENUM_VALUE for every item returned from the split.

This returns PnumID and its EnumvalueName in tabular format. Do you want to convert the EnumvalueNames into comma separated list?

declare @Pnuminput nvarchar(100) = '10000,10001,10002'

select t1.Pnum, E1.EnumvalueName
  from (
SELECT ltrim(SUBSTRING(Pnuminput, n, CHARINDEX(',', Pnuminput + ',',n) - n)) AS Pnum
 FROM (select @Pnuminput Pnuminput) t1
CROSS JOIN (SELECT number FROM master..spt_values WHERE type = 'P')  AS Numbers(n)
WHERE SUBSTRING(',' + Pnuminput, n, 1) = ','
  AND n < LEN(Pnuminput) + 1 ) t1
  join Dbo.CL_ENUM_VALUE AS E1 where E1.Id = t1.Pnum

Select allOpen in new window

Hi Olaf

I checked your solution and it works , however one little issue when the first character of @pnuminput is '-' . I was trying to put an expression using my vba knowledge and make some changes in SQL .

If left(@pnuminput, 1)
Begin
@pnuminput = right(@pnuminput , len(@pnuminput)-1)
End

I also assume @data is a temporary table and I had to insert values of this table into this . Assume this is correct as it worked . I could not simply use @data = select ....

Thanks

FYI: SQL Server 2016 includes a built-in STRING_SPLIT function for v13 databases ... finally!

Your assumption about the reason for @data is wrong. It is simply my sample codes replacement for your real table Dbo.CL_ENUM_VALUE, you can simply remove its definition and the insert into it, as you have your data.

The solution to your problem of a prefixed '-' almost works, you just forgot ...= @Delimiter, it has to be If left(@pnuminput, 1) = @Delimiter, without the comparison there is no boolean result.

And once again, you forgot the SET. Assignments in T-SQL need the SET command, expressions as @var = value are not seen as assignments, but as comparison. The alternative is within a SELECT, as also used in the code, but also not in any SELECT. Refer to the reference to pick up what I try to teach you here, please:
1. SET @var=...: https://msdn.microsoft.com/en-us/library/ms189484.aspx
2. SELECT @var=...:https://msdn.microsoft.com/en-us/library/ms187330.aspx

Bye, Olaf.

Sorry, didn't have time to do actual code when I first posted.

Again, forget about looping!  It's a performance killer in SQL.  For relational languages, you need to think set-based rather than loop-based.

If you want to use a function, use an inline table-valued function so that it performs well if you need to decode an entire table's worth of codes.

Here's the main function; it uses the high-performing dbo.DelimitedSplit8K function I mentioned earlier: you can Google the source for that function:

CREATE FUNCTION Closure.PLookup3
(
    @Pnuminput varchar(255)
)
RETURNS TABLE
AS
RETURN (
    SELECT STUFF((
        SELECT ',' + E1.EnumvalueName
        FROM dbo.DelimitedSplit8K(@Pnuminput, ',') ds
        LEFT OUTER JOIN dbo.CL_ENUM_VALUE E1 ON E1.Id = LTRIM(RTRIM(ds.Item))
        ORDER BY ds.ItemNumber
        FOR XML PATH('')
        ), 1, 1, '') AS return_values
)
GO


Here's some sample code-table values:

CREATE TABLE dbo.CL_ENUM_VALUE (
    Id int PRIMARY KEY,
    EnumvalueName varchar(255) NOT NULL
    )
INSERT INTO dbo.CL_ENUM_VALUE VALUES(10000,'String10000'), (10001,'String10001'), (10002,'String10002')
*/


And here's the code to do the actual decoding.  Note how quickly this code runs!

DECLARE @input_values varchar(8000)
DECLARE @return_values nvarchar(4000)
SET @input_values = '10000, 10001, 10002'

SELECT *
FROM Closure.PLookup3(@input_values)

SELECT @return_values = return_values
FROM Closure.PLookup3(@input_values)
PRINT @return_values

Did you try other posts?

While I agree with Scott in general, it looks really odd to use a splitter function and then look up single values to then concatenate them using XML PATH.

I instead just gave you, what you asked for, but I'd go for yet another solution.

Using a splitter stored proc and XML PTAH are the usual ways to do this kind of thing in T-SQL and fortunately SQL 2016 introduces a Split_String() function to put one of them at rest, but more natural usage of a series of IDs, of course, is to put them into a table, perhaps a temp table, and inner join this set of IDs. And a natural result of such a query rather is a set of values than a concatenation of them.

This very obviously looks like something done outside of the core intended service of a database. I hope one day MS will also introduce something like Concat_String and let XML PATH be used to process something else, perhaps XML data. Judging from the name, it looks like it might be useful there.

The most natural way to go about this, in my opinion, would be creating a temp table, inserting the single IDs - perhaps for that to work get rid of data with dash separated IDs but instead, have a detail table and then do a classical join with the result also being a set of values.

That set of values arriving anywhere client side can afterwards be iterated and concatenated, because that indeed is not a thing to do in T-SQL, really, but is dead easy in any client side multi-purpose programming language. PHP has explode/implode, C#, Python, and even Javascript have String.Join() or Array.Join() to convert lists or arrays to a single string.

Bye, Olaf.

Perfect . Great stuff guys and thanks for the reference information . Much appreciated again . Have a good weekend .

While I agree with Scott in general, it looks really odd to use a splitter function and then look up single values to then concatenate them using XML PATH.
...
Using a splitter stored proc and XML PTAH are the usual ways to do this kind of thing in T-SQL and

Isn't that self-contradictory?  If it's the "usual way", how can it be "odd"?  

And why is it "odd" anyway?  You have to look up the values individually, as they appear in the lookup table as single entries, not combined.  How else, then, could one look them up except to split them first and then look each up separately??

What's "odd" to me is to encourage any type of looping within a set-based language such as SQL, unless such a loop is absolutely unavoidable.  The poor performance of such code is just another reason to avoid it.

As to whether this should/should not be done within the db language, that's a different q to me.  The q here was: once I've chosen to do this within the dbms engine, what's the best way to do it?

Finally, as much as we DBAs/developers like result sets, sometimes real-world applications need a singe string of results rather than an array.  For example, when I write emails to several people, I like being able to list "name1; name2; ..." rather than me having to create a "name_table".

How can a usual way be odd? Well, can't you see this yourself? Why should we need such a complex way to first create a tally table large enough to be able to create all the possible substrings and take out those splitting exactly at delimiters. This really is the pride in managing to do something with the limited possibilities of SQL, while it's just straight forward 𝒪(n) complexity - linear to the length of the string to split. It's a simple iteration to go through a string and split it at delimiters found while iterating from position 1 to length.

This always wasn't awesome to me, but crap. It might awesome in the aspect to manage this, but it's really making a bad impression to me about Microsoft or any RDBMS vendor not to have come up with a String_Split() function earlier if you see how often a splitter stored proc is used. And now they had the kindness to do this not to come up with the inverse aggregation function. MySQL has GROUP_CONCAT() for example, Oracle has LISTAGG.

The word for doing such simple things in the way they are done may best be described as awkward. And I see a tendency of people bragging with this knowledge of this splitter functions and the performance they managed to get from them, while this is something you should rather point to in shame of not having something more straight forward.

Bye, Olaf.

I didn't design or write SQL Server's code and must work within what it has, not refuse to write efficient code because some other language might have a capability that SQL current doesn't.  If you're ashamed of having to work within the restrictions of any given language, perhaps you shouldn't be a developer, since no one language can offer all things to all devlopers.

I don't consider lack of a string split function to be a horrible handicap for a relational dbms, particularly given that SQL provides the capability to create our own functions.

And what I agree with is that the capability to do while loops in T-SQL can be seen as implemented just for the sake of completeness of a formal language including simple program flow paradigms. Queries are indeed loops in themselves, they iterate on sets, but in a less ordinary way of the "full table scan" simple iterations any simple for loop would do. So given the situation as is, implementing a loop within a stored proc only can be argued for to have a better case than just an academic example to learn a bit about how loops could work and the lesson learned from yasanthax or what he should have learned here seems rather to be about SET and the paramterization of SUBSTRING and CHARINDEX than about WHILE.

So in short I seconde given the situation of T-SQL this specific problem it's still not a case for using WHILE but rather what you can use today. Configuring SQL SErver to be able to program in C# could improve this, but has it's own pros and cons.

You know and recommend the article of Jeff Moden and you know it ends in showing how a Split based on CLR (c#) is fastest anyway. Which proves to me, this is the job of the RDBMS vendor to add it as a native functionality done in C# or perhaps C++, but it was a missing portion of T-SQL so far.

Bye, Olaf.

And finally you miss a simple point of what yasanthax said:

This is also for me to improve my knowledge of SQL loops

So I gave that lesson to be able to see a working loop.

Bye, Olaf.

I didn't miss that point.  I made the counter-point that you should not focus on writing loops in SQL Server, but instead focus more on writing set-based solutions.  SQL isn't C++ and shouldn't be written the same way.  You need to learn to think in terms of sets, not individuals rows.

Starting to learn SQL by studying loops is terribly counter-productive.  You should be studying types of joins, etc., not how to code loops.

Similarly, if you want to update every row that contains a NULL value in a given column, you should do this:
UPDATE table_name
SET col1 = ''
WHERE col1 IS NULL

And not as below, even though, yes, the code below would allow you to practice writing loops(!):

DECLARE @id int
DECLARE csr_col1 CURSOR FOR
SELECT id /*assume id is a unique key for the row*/
FROM table_name
WHERE col1 IS NULL

OPEN CURSOR csr_col1

WHILE 1 = 1
BEGIN
    FETCH NEXT FROM csr_col1 INTO @id
    IF @@FETCH_STATUS <> 0
        BREAK
    UPDATE table_name
    SET col1 = ''
    WHERE id = @id
END /*WHILE*/

DEALLOCATE csr_col1

Scott, you really are very inattentive. The quote I gave was not quoting myself but the Author of this question. You just have to scroll back to the question itself to find it there, or press CTRL+F and search. Anyway, that misunderstanding from your side is easily solved, all you say is addressed to him and I don't feel bad about this, I rather feel sorry for you.

I do agree with you, that SQL Servers should be used with set based code, eg SQL. In other occasions, I also said some functions should rather not be available in T-SQL at all, but I don't think so in regard to IF and WHILE loops, they have their raison d'être.

And while this isn't an ideal case to implement a loop, its not so bad as your UPDATE on NULL condition. So in your opinion yasanthax should perhaps never ever learn loops in T-SQL, I rather helped him understand his errors, and knowledge about how and where to set variables surely is helpful also outside of loops, so this is really an improvement of skills. If you like you can put all other commands but INSERT, SELECT, UPDATE and DELETE aside, create tables with the visual frontend the SSMS offers and can do a CRUD API interface to a database and everything else you can't do with this subset you can do clientside. Fine. I'm overexaggerating your point of view, but that's how I see your stance on this.

And on the piont that "no one language can offer all things to all devlopers": I didn't made the point that any language should be universal, I made the point any language should be pure for it's intention. And for SQL that would include such split CSV (more precise one row of CSV rather is one record, so we talk of UNPIVOTING that, but simply take "comma separated values" in that literal sense and that also applies to '1,2,3') to rows and concat rows to CSV. It's not the core of SQL, but it obviously is helpful and even part of SQL2011 ANSI standard.

Anyway, like so many times already, we can agree to disagree and lay this down at this point.

Bye, Olaf.

Hardly inattentive.  I quite noticed that you excused your recommendation for poor coding on the initial q; impossible to miss really.  But, as experts, we're supposed to provide guidance when the original poster is not aware of some capabilities of SQL and/or the best approach to take.  Loops have their place in SQL, but that task is not properly one of them.

I'm the one trying to provide professional-level methods for doing specific tasks in SQL, using the currently best techniques available, rather than using lowest-common-denominator loops.  Just because you aren't familiar with a techniques does not make it "odd" or improper.  The approach I recommended is well-tested, proven and generally accepted as best practice by true SQL experts.

Finally, it's absolutely bizarre to insist that the "pure intention" of SQL would have a string split function.  The underlying theory of SQL is based on atomic (single-value) columns.  Reference Codd's rules and normal data forms.  Thus, a splitter function is a great extension to the language, but hardly a core requirement.

I'm almost fine with that, except two things, unfortunately the core things and not just side notes.

1. Our task as experts
I think even yasanthax himself knows it's not the first class solution to do a loop, therefore he explicitly asked how this task could be done so. In the light of the lack of not knowing several basic things one could also see this different, but if you never do anything but query language, you can't get well-versed on T-SQL's part of structural programming. He damnded to be teached what I answered and not doing and even with the best intention of it I can think of, I consider that arrogant towards yasanthax and not helpful and corrective.

Edit: To be more precise, you can give the code asked for and the better alternative on top of that, instead of withholding any help just because of your expertise on it and even when you consider having all experts on your side.

I have your stance from time to time, but only if I see something dangerous in what is asked for, not if it's just a less good solution.

2. Splitter function

A splitter function is a great extension to the language, but hardly a core requirement.

Well, SQL has evolved from that core requirements and when even the American National Standards Institute considers that to be part of the SQL language and several competing databases implement this, it is a shame Microsoft is last - again.

Bye, Olaf.

If I made the first contribution to this question, it would read:

I don't recommend looping in SQL. However, to answer your question, perhaps an expert will comment as to the best way to loop, as I am not  an expert at looping in SQL.

In situations where I perceive a pure SQL solution is messy or overly-complicated, I use .NET processing to resolve all other issues. Changes even in the most complex transformations are a breeze that way.  A pure SQL solution may require that SQL do things it's not very good at. Same thing goes for trying to make .NET do things SQL does better.  I let each tool do what it does best in concert,

Good luck!

Hi Yasanthax,
Please confirm you have no more questions to this thread. Thanks