Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I can think of several options:</p> <ol> <li><p>Use a session-keyed table: delete rows matching current spid, insert desired rows with current spid, read from table in SP, delete from table (again).</p></li> <li><p>Make your client submit a query with many OR ... LIKE ... clauses.</p></li> <li><p>Write an SP that does the same thing as your function and returns a recordset. <code>INSERT YourTable EXEC SP @Strings</code> and you are done!</p></li> <li><p>Use the numbers-table-charindex-into-string inside of a derived table method of splitting the string.</p></li> </ol> <p><strong>Example</strong></p> <p>Let me flesh this out a little for you with an example combining ideas #3 and #4. Of course, your code for your function could be adapted, too.</p> <p>Build a separate <code>Numbers</code> table. Here is example creation script:</p> <pre><code>--Numbers Table with 8192 elements (keeping it small for CE) CREATE TABLE Numbers ( N smallint NOT NULL CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED ); INSERT Numbers VALUES (1); WHILE @@RowCount &lt; 4096 INSERT Numbers SELECT N + (SELECT Max(N) FROM Numbers) FROM Numbers; </code></pre> <p>The SP:</p> <pre><code>CREATE PROCEDURE dbo.StringSplitRowset @String varchar(8000) AS SELECT Substring(@String, l.StartPos, l.Chars) Item FROM ( SELECT S.StartPos, IsNull(NullIf(CharIndex(',', @String, S.StartPos), 0) - S.StartPos, 8000) FROM ( SELECT 1 UNION ALL SELECT N.N + 1 FROM Numbers N WHERE Substring(@String, N.N, 1) = ',' ) S (StartPos) ) L (StartPos, Chars); </code></pre> <p>And usage, easy as pie:</p> <pre><code>DECLARE @String varchar(8000); SET @String = 'abc,def,ghi,jkl'; CREATE TABLE #Split (S varchar(8000)); INSERT #Split EXEC dbo.StringSplitRowset @String; SELECT * FROM #Split; </code></pre> <p>Result: </p> <pre><code>abc def ghi jkl </code></pre> <p>And finally, if you don't want to build a numbers table, you can use this SP. I think you will find that one of these two SPs performs well enough for you. There are other implementations of string splitting that could work as well.</p> <pre><code>ALTER PROCEDURE dbo.StringSplitRowset @String varchar(8000) AS SELECT Substring(@String, l.StartPos, l.Chars) Item FROM ( SELECT S.StartPos, IsNull(NullIf(CharIndex(',', @String, S.StartPos), 0) - S.StartPos, 8000) FROM ( SELECT 1 UNION ALL SELECT N.N + 1 FROM ( SELECT A.A * 4096 + B.B * 1024 + C.C * 256 + D.D * 64 + E.E * 16 + F.F * 4 + G.G N FROM (SELECT 0 UNION ALL SELECT 1) A (A), (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) G (G), (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) F (F), (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) E (E), (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) D (D), (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) C (C), (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) B (B) ) N (N) WHERE Substring(@String, N.N, 1) = ',' ) S (StartPos) ) L (StartPos, Chars) </code></pre> <p>Any SQL writer serious about understanding some of the performance implications of splitting strings different ways ought to see <a href="http://sqlperformance.com/2012/07/t-sql-queries/split-strings" rel="nofollow">Aaron Bertrand's blog post on splitting strings</a>.</p> <p>Also, any serious SQL Server database student ought to see <a href="http://www.sommarskog.se/share_data.html" rel="nofollow">Erland Sommarskog's How to Share Data between Stored Procedures</a>.</p>
    singulars
    1. This table or related slice is empty.
    1. POT-SQL LIKE condition on comma-separated list
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USErikE
    1. USErikE
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POT-SQL LIKE condition on comma-separated list
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COThanks plenty to go on here.
      singulars
      1. PO
      1. USnfplee
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload