Query for below pattern in Sql server
sql contains multiple values
multiple like in sql
sql like escape
sql like regex
sql like multiple words
sql like number
sql server in clause
I am trying to achieve the output shown at bottom, but do not get the idea how can it to be done.
#T2 are the input tables.
CREATE TABLE #T1 (ID VARCHAR(10)) INSERT INTO #T1 SELECT 'A' UNION SELECT 'B' UNION SELECT 'C'
CREATE TABLE #T2 (ID VARCHAR(10)) INSERT INTO #T2 SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
and the expected output by using the
OUTPUT ID ID A 1 B 2 C 3 A 4 B 5 C 6 A 7
It's not particularly pretty, however:
WITH T1 AS( SELECT ID, ROW_NUMBER() OVER (ORDER BY ID) AS RN FROM #T1), T1M AS( SELECT *, MAX(RN) OVER () AS MAXRN FROM T1), T2 AS( SELECT ID, ROW_NUMBER() OVER (ORDER BY ID) AS RN FROM #T2) SELECT T1.ID AS T1ID, T2.ID AS T2ID FROM T1M T1 JOIN T2 ON T1.RN = CASE T2.RN % T1.MAXRN WHEN 0 THEN T1.MAXRN ELSE T2.RN % T1.MAXRN END ORDER BY T2.ID ASC;
Edit: note, this assumes that T1 has less rows than T2. if the inverse is true, this won't work.
SQL Wildcard Characters, Wildcard Characters in SQL Server Demo Database. The table below shows the complete "Customers" table from the Northwind sample database: Learn SQL by doing 50+ interactive coding exercises. Start now!
You can use
with t1 as ( select *, ROW_NUMBER() over (order by id) Seq from #t1 ), t2 as ( select *, 1+(ROW_NUMBER() over (order by id)-1) % (select COUNT(1) from t1) Seq from #t2 ) select t1.Id, t2.Id from t2 inner join t1 on t1.Seq = t2.Seq order by t2.Id;
SQL LIKE Operator, WHERE columnN LIKE pattern;. Tip: You can also combine any The table below shows the complete "Customers" table from the Northwind sample database: PATINDEX (Transact-SQL) 07/19/2016; 3 minutes to read +5; In this article. APPLIES TO: SQL Server Azure SQL Database Azure Synapse Analytics (SQL DW) Parallel Data Warehouse . Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data
Try this one, it's bit longer process in the row numbering and grouping, but pretty much that's all the processes, putting on row number and grouping, pretty simple
with cte as ( SELECT ID, ceiling(cast(row_number() over (order by ID ASC) as decimal)/ 3) as grp FROM #T2 ) SELECT T1.ID, T2.ID FROM (SELECT ID, cast(row_number() over (order by ID ASC) as decimal) as grp FROM #T1) T1 LEFT JOIN (SELECT ID, cast(row_number() over (PARTITION BY grp order by ID ASC) as decimal) as grp FROM cte) T2 ON T1.grp = T2.grp ORDER BY T2.ID
the result is as follows
Print different star patterns in SQL, Let's see how we can print the pattern of various type using SQL. Syntax : Declare @variable_name DATATYPE -- first declare all the -- variables with datatype There are 11 star pattern examples in this tutorial. I will add more example… In Sql we use replicate function to print pattern easily. How Replicate Function Works In Sql The Replicate funct…
SQL Fiddle: http://sqlfiddle.com/#!18/03a3f/39
SELECT T1.ID , T2.ID FROM (SELECT ROW_NUMBER() OVER (ORDER BY ID ASC) AS ROW_NUM , T2.ID FROM T2 ) AS T2 INNER JOIN (SELECT ROW_NUMBER() OVER (ORDER BY ID ASC) AS ROW_NUM , (SELECT COUNT(*) FROM T1) AS SEQ_COUNT , T1.ID FROM T1 ) AS T1 ON T1.ROW_NUM = CASE (T2.ROW_NUM % T1.SEQ_COUNT) WHEN 0 THEN T1.SEQ_COUNT ELSE (T2.ROW_NUM % T1.SEQ_COUNT) END ORDER BY T2.ROW_NUM ASC;
+----+----+ | ID | ID | +----+----+ | A | 1 | | B | 2 | | C | 3 | | A | 4 | | B | 5 | | C | 6 | | A | 7 | +----+----+
SQL Server LIKE Operator By Examples, This tutorial shows you how to use the SQL Server LIKE operator to check whether a A pattern may include regular characters and wildcard characters. For example, the following query finds the customers where the first character in the A wildcard character is used to substitute one or more characters in a string. Wildcard characters are used with the SQL LIKE operator. The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
need help for my query to get the number pattern. – SQLServerCentral, Dear all,. I am trying to make a triangle pattern using number. so please help me ,and my query is below declare @n1 int. declare @n2 int. In the above query the pattern is (A B+ C+), meaning (any row, followed by one or more rows with decreasing prices, followed by one or more rows with increasing prices). Following are regular expression pattern quantifiers that you can use: By default the quantifiers are greedy, but you can define them to be reluctant.
Examples and Function for Using SQL Server LIKE Operator and , Have you ever used the SQL Server LIKE operator and were show how to build SQL Server queries including the LIKE operator with a pattern Below is a list of wildcard characters according to Microsoft's documentation:. We use regular expressions to define specific patterns in T-SQL in a LIKE operator and filter results based on specific conditions. We also call these regular expressions as T-SQL RegEx functions. In this article, we will use the term T-SQL RegEx functions for regular expressions.
Do Partial Matches Using LIKE, Learn how to use the sql LIKE operator to perform pattern matching. to find all ID's that didn't match a social security number you could write your query as: How many orders were ordered in 2012 with a freight charge less than $7.00? SQL Server, via the Management Studio GUI, execution plan XML, or missing index DMVs, will let us know when there are missing indexes that could potentially help a query perform better: This warning is useful in that it lets us know that there is a potentially easy fix to improve query performance.
- Is your output complete ? What is the logic behind this ?
- impossible to answer because we do not know the logic that you used to create your output
- As per T2 table T1 table's data will increment.. as it has only a,b,c so after after A,B,C it will start once from A like that
- This looks like something that's a better fit for a spreadsheet rather than a relational database. Is there a specific reason you're doing this in SQL?
- but then T2 is useless, you just want to increment records on T1?
- @AryanSena I wouldn't say the above is particular complex, it's just making use of
CASEexpression; just not pretty,
- You can eliminate the
ON T1.RN % T1.MAXRN = T2.RN % T1.MAXRN.
- Good concept - couple of modifications. Note that you can use % (modulo) instead of the ceiling/division logic. Also, the OP cannot necessarily hard-code a 3, which is why my solution counts the rows in T1.