63961

I want to return the same item twice in SQL Server

A very simple question. Couldn't find something similar here (maybe I didn't use the right wording. Sorry in advance).

I have a table:

| ID | FullName | ------------------- | 1 | Frank Owa | | 2 | Gail Sorbe |

I wish the query to display all the users that contains the string either 'ran' or 'wa'.

And that includes duplicates.

So, what I expect to get is:

|ID | FullName | ----------------- |1 | Frank Owa | |1 | Frank Owa |

Because Frank Owa has either 'ran' or 'wa'. And I wish to display it twice as a result.

But the query will display the result only once.

SELECT * FROM Table WHERE FullName LIKE '%ran%' OR FullName LIKE '%wa%'

What did I do wrong?...

Thanks

Answer1:

The query checks on an either-or basis - if a row meets any of the 2 conditions, it is included once and then the next row is checked. If you want it to appear twice on account of meeting 2 different conditions, you could use UNION ALL, like so:

SELECT * FROM Table WHERE FullName LIKE '%ran%' UNION ALL SELECT * FROM Table WHERE FullName LIKE '%wa%'

Answer2:

Use UNION ALL

SELECT * FROM TABLE WHERE FullName LIKE '%ran%' UNION ALL SELECT * FROM TABLE WHERE FullName LIKE '%wa%'

Answer3:

You can do a Union All between queries that fetch names containing 'ran' and and query that fetches 'wa'.

SELECT * FROM Table WHERE FullName LIKE '%ran%' UNION ALL SELECT * FROM Table WHERE FullName LIKE'%wa%'

Answer4:

Since the condition is dynamic and you cannot write UNION ALL every time you have to do it dynamically.

You sample table

SELECT * INTO #TABLE FROM ( SELECT 1 Id, 'Frank Owa' FullName UNION ALL SELECT 2,'Gail Sorbe' )TAB

Now you need to split the values and insert into a temparery table.

DECLARE @Split VARCHAR(1000); SET @Split = 'ran,wa'; DECLARE @x XML; SET @x = '<r>' + REPLACE((SELECT @Split FOR XML PATH('')), ',', '</r><r>') + '</r>'; SELECT ROW_NUMBER() OVER(ORDER BY y.XmlCol.value('(text())[1]', 'VARCHAR(1000)'))RNO, y.XmlCol.value('(text())[1]', 'VARCHAR(1000)') AS Value INTO #TEMP FROM @x.nodes('/r') y(XmlCol);

Now, the following code will write UNION ALL dynamically for you

DECLARE @I INT=1 DECLARE @COUNT INT DECLARE @QRY NVARCHAR(MAX) DECLARE @CONDITION NVARCHAR(MAX) DECLARE @NAME NVARCHAR(MAX) SELECT @COUNT=COUNT(*) FROM #TEMP -- Loop will be executed for the number of parameters, ie, for your above example, -- @COUNT will be 2. WHILE(@I <= @COUNT) BEGIN -- Select ran on first loop wa on second loop etc SELECT @NAME = Value FROM #TEMP WHERE RNO= @I SET @CONDITION = 'SELECT * FROM #TABLE WHERE FullName LIKE ''%' + @NAME + '%'' UNION ALL ' SELECT @QRY = COALESCE(@QRY,'') + @CONDITION SET @I=@I+1; END

Now you need to remove last UNION ALL and execute the query dynamically

SELECT @QRY = LEFT(@QRY, LEN(@QRY)-9) EXEC SP_EXECUTESQL @QRY

Here is the result

<img src="https://i.stack.imgur.com/qdlo5.jpg" alt="enter image description here">

Recommend

  • TSQL Distinct Counts
  • Does Outlook 2003 support iCal 2.0 spec for updating and deleting a calendar item?
  • Collation conflict SQL Server 2008
  • MvcSiteMapProvider preserved route parameter gives error “Could not resolve URL for sitemap node”
  • Issue Using PIVOT in SQL
  • How to transpose columns to rows in sql server
  • How do I retrieve Google Calendar Resources for my company using the PHP API client?
  • Core animation stops when the Master of the split viewcontroller hide (in portrait mode)?
  • Creating a table in “MSWord” document out of a text in a .txt file
  • MSVC10 /MP builds not multicore across folders in a project
  • How to check whether the file exist in HDFS location, using oozie?
  • JS Switch case not working correctly always default is executed
  • How to count amount of elements in a row of a matrix in C
  • Linq Full Outer Join on Two Objects
  • MySQL multiple IN conditions to subquery with same table
  • Generating anchors with PyYAML.dump()?
  • distinct values from multiple fields within one table ORACLE SQL
  • Configure Spring's MappingJacksonHttpMessageConverter
  • XSLT foreach repeating nodes to flat
  • Exception gevent.hub.LoopExit: LoopExit('This operation would block forever',)
  • Error in installing package: fatal error: stdlib.h: no such file or directory
  • Does Apple allow the usage of sysctl.h within iOS applications?
  • how to set variables in a php include file?
  • How to autopopulate a field in SugarCRM form
  • How to make R's read_csv2() recognise the text characters properly
  • Eloquent paginate function in Slim 3 project using twig
  • How to add git credentials to the build so it would be able to be used within a shell code?
  • How to write order and limit within cakephp joins array
  • CakePHP 2.0.4 - findBy magic methods with conditions
  • Can you perform a UNION without a subquery in SQLAlchemy?
  • Meteor: Do Something On Email Verification Confirmation
  • Scrapy recursive link crawler
  • Java Scanner input dilemma. Automatically inputs without allowing user to type
  • Deselecting radio buttons while keeping the View Model in synch
  • Nant, Vault & Windows Integrated Authentication
  • What is the “return” in scheme?
  • Where to put my custom functions in Wordpress?
  • RestKit - RKRequestDelegate does not exist
  • WPF Applying a trigger on binding failure
  • -fvisibility=hidden not passed by compiler for Debug builds