Does something like this satisfy the resultset?
Table & Data
CREATE TABLE #TestingIdea (
Id int PRIMARY KEY IDENTITY (1,1),
PostId int NULL,
Tag nvarchar (MAX) NULL
INSERT INTO #TestingIdea(PostId,Tag)
SELECT PostId, RIGHT(value,len(value)-1) as SplitTag
CROSS APPLY ...
I don't actually have a sybase system to test with, but according to http://dcx.sybase.com/1200/en/dbreference/sa-split-list-sysproc.html the following solution should work:
FROM table AS T
JOIN sa_split_list('1234,23,56,576,1231,567,122,87876,57553,1216') AS L
ON T.id = L.row_value;
If the id_list is actually stored in another table (say ...
Well, this is really awful, but okay, if you are going to refuse to even consider better alternatives... first, create a set-based split function that will track the order of the string (your looping function is really not optimal):
CREATE FUNCTION [dbo].[SplitStrings_Ordered]
I recommend using a flavor of method 2 and splitting the search range into many target tables. 10000 tables is a good first attempt. For example, if you search for "012345678901" then your query would look at the table associated with data that starts with "0123". You would still have about a trillion rows in total, but
splitting the data into many tables ...
Storing and processing 1TB of data with only 16GB RAM available may prove to be a challenge. 1GB per core is rather unbalanced, especially for this kind of workload. 8GB per core would be a much better starting point, with more desirable.
That said, I would still be tempted to try a variation of method 2:
Store all possible 12-character substrings as ...
A possible solution might be Splitting Delimited Strings Using XML in SQL Server. From that post:
This article will help developers looking for a way to split delimited
strings in a single query using XML. We generally use a user defined
function to do this, which you have probably found in many places that
splits the string based on ...
The biggest differences between a hard-coded IN list and the values generated by STRING_SPLIT are:
The optimizer automatically sorts and removes duplicates from a literal IN list at compile time. This gives the optimizer accurate information about the number and distribution of values.
Literal values can be embedded in a data access operator (e.g. an index ...
The mongos processes control when automatic splitting happens (you can also pre-split, and split manually). The heuristic they use is a bit more complicated than I am about to describe, but you can use it as a rough guide:
Each mongos keeps track of data written to a particular chunk
At ~20% of the max chunk size (default is 64MB), it will try an autosplit
The built-in STRING_SPLIT() function was added in SQL Server 2016, not 2014. And for me, I go the SQLCLR route because I am not using Azure Single DB or AWS RDS and thus have no reason not to. I use the SQLCLR library that I created, SQL#, which contains String_Split and String_SplitInts (optimized for splitting a list of BIGINT / INT / ...
RegEx is the only serious way to do this. Here's a simple example using the RegEx functions that come installed for free if you have Master Data Services installed. Otherwise google around for SQL Server CLR RegEx, the expression used should be transferrable:
-- Find and replace commas in quotes
DECLARE @t TABLE( yourText VARCHAR(MAX), newText ...
You can use the Table Value Constructor in a cross apply to generate two rows for each row in the source table.
declare @T table
ID int identity primary key,
insert into @T values
('2016-10-01T10:10:00', '2016-10-01T10:50:00', '2016-10-01T10:00:00', '2016-10-...
In this spirit of showing a man how to catch a fish, I'll provide a few possibilities that should point you in the right direction. First, we need to have a test table, with some test data, so we can try some things:
We'll do this in tempdb, so we don't affect anything important:
Here, we'll create the table:
IF OBJECT_ID(N'dbo.Split', N'U')...
Because I've not seen either of the following approaches listed yet, I figured I'd include them.
If you're running SQL 2016 or later, take a look at the STRING_SPLIT function.
If you're running SQL 2014 or earlier, you can utilize some XML trickery to split up the string into a table.
Both approaches are as follows:
DECLARE @myString NVARCHAR(400) = '12,...
I don't think your sys.all_objects has enough rows to make this work. It looks like your split routine is reading each individual character and concatenating it. You have more characters in your stored procedure than you have rows in sys.all_objects.
I created and populated a dbo.numbers table with 100,000 rows and modified your code to use that instead ...
You can't pass a query into a function like that. How about using CROSS APPLY:
SELECT tbl.<KeyColumn>, f.Name
FROM dbo.MyTable AS tbl
CROSS APPLY dbo.fnSplitString(tbl.Field, ',') AS f;
Of course in SQL Server 2016 there is native support for this now, with the new STRING_SPLIT function, instead having to write your own (less efficient) function:...
Create the function in msdb or somewhere else.
CREATE FUNCTION dbo.SplitTwoStringsWithSameOrder
WITH src(r) AS
SELECT 1 UNION ALL SELECT r + 1 FROM src WHERE r < 10
While this question, for the most part, has been asked and answered what at least feels like hundreds of times on here and StackOverflow, because it concerns int values only I will say that:
Given that you are dealing with only INT values (as the values contained in the delimited string), the fastest way I have come across is to use a splitter that is a) ...
I'd access this first at the point where those data are created - replace whatever mechanism you use (looks like self-built using carriage-return chr(10) as divider ) - by a proper json serialization - which enables simple json de-serialization
To parse the existing data in said column - you can use SUBSTR https://www.techonthenet.com/oracle/functions/...
The below example should work in SQL 2012. It assumes your string will always feature 3 alphabetical characters, a space, a dollar sign and a comma separating individual values. If this is not the case, you will either need to data cleanse or amend the script to handle the different scenarios.
DECLARE @Str VARCHAR(MAX) = 'ABC $3.25, DEF $2.50, HIJ ...
If your SQL version is 2016 or later, you could try to use STRING_SPLIT function.
Against earlier version, there are also lots of impletmentations of string split by searching GG.
Below is my example for SQL 2016+:
DECLARE @v nvarchar(256) = N'ABC $3.25, DEF $2.50, HIJ2 $0.25,ABC1 $3.25, DEF $2.50, HIJ $0.25, KLM $4.50'
SELECT TRIM(value), RIGHT(value, ...
STRING_SPLIT() is probably best in SQL Server 2016. I've tested against 1 million rows and returns results in 12 seconds (fairly contrived test).
You can use the PIVOT operator to produce the columns from your rows following the split as there are a known number of elements.
Code to setup
CREATE TABLE #Table (ID INT IDENTITY, StreetAddress VARCHAR(MAX))
If you will be constantly parsing the data out on the fly, you should probably just stored it parsed in the first place.
If you do want to store it as flat strings there are some things you can do to speed it up.
where uri like 'A/B/C/D%'
This part can be sped up creating an index
create index on meta_info (uri text_pattern_ops);
How much of a speed ...
I would do something like this,
create a helper function to generate your query terms from the text-string input. You could inline this into the function, but it's ugly and silly and if you need it here then you probably need it elsewhere.
Write a query with NOT word LIKE ANY()
Here is a demo of the helper function,
CREATE OR REPLACE FUNCTION ...
If you're not against using dynamic SQL, the following example should serve your purpose. @Inlist is my substitute for your table0.params (I commented out the actual EXEC command.)
DECLARE @InList VARCHAR(100)
SET @InList = '12,13,14'
DECLARE @sql NVARCHAR(1000)
SET @sql = 'SELECT * ' + 'FROM MyTable ' + 'WHERE Id IN (' + @InList + ') '
The best way I found, to use STRING_SPLIT in these scenarios is to not use an IN clause at all, but to do an inner join on the results of string_split instead. This can often stop table scans (with multiple In clause values) and make the SQL optimiser use an available index instead. So:
WHERE s.sample_id in(2495,2496,2497,2498,2499,2500,2501,2502,2503,...
I assume that that you have 3 tables with identical structure, where the first has the data while the two others are empty.
I also assume that the tables or at least table1 has a primary key.
Searching through the Impala SQL documentation, I don't find any window functions, so a ROW_NUMBER() solution is out of the question.
We could use a random function ...