T-SQL (SS2K8) :: Add String WHERE Filter That Will Match Everything
Nov 19, 2014
I need to add a filter clause like
WHERE username = '%%%'
I know you will say 'why add a filter if you're not going to use it?' but I need to for a certain application which will use the parent query for child queries in which I select the specificity required for the child query's data set.I've tried '%*%' and '%_%' but always it returns nothing. I need the filter to exist yet not really filter.
I remember in the past using IsNull in a where filter of a stored proc that was used for a search form. I typically go about this in the following way. Is threre a better way to handle this?
I need to query to return a result for each unique machine with the latest date. The example result below would be returned because they have the latest date.
MachineA 5/7/2011 MachineB 5/5/2010
Select Distinct would almost do it, but I need each unique machine that has the latest date.
In short: I want my following problem to work with a LIKE instead of exact match and if possible be faster. (currently 4s)
Problem: I got a set of rows with varchar(50), spread out over multiple tables. All those tables relate to a central Colour table. For each of the columns, I want to match the values with a set of strings I insert and then return a set of Colour.Id
E.g: input: 'BLACK', 'MERCEDES', '1984' Would return colour ids "025864", 45987632", "65489" and "63249" Because they have a colour name containing 'BLACK' or are on a car from 'MERCEDES' or are used in '1984'.
Current Situation: I) Create a table containing all possible values CREATE TABLE dbo.CommonSearch( id int IDENTITY (1, 1) NOT NULL, clr_id int, keyWord varchar(40), fieldType varchar(25) And fill it with all the values (671694 rows) ) II) Stored Procedure to cut a string up into a table: CREATE FUNCTION dbo.SplitString ( @param varchar(50), @splitChar char = '' ) RETURNS @T TABLE (keyWord varchar(50)) AS BEGIN WHILE LEN( @param ) > 0 BEGIN declare @val varchar(50) IF CHARINDEX( @splitChar, @param ) > 0 SELECT @val = LEFT( @param, CHARINDEX( @splitChar, @param ) - 1 ) , @param = RIGHT( @param, LEN( @param ) - CHARINDEX( @splitChar, @param ) ) ELSE SELECT @val = @param, @param = SPACE(0) INSERT INTO @T values (@val) END RETURN END III)Stored Procedure to query the first table with the second one CREATE PROCEDURE [dbo].[GetCommonSearchResultForTabDelimitedStrings] @keyWords varchar(255) = '' AS BEGIN SET NOCOUNT ON; select clr_id, keyWord, fieldType from dbo.commonSearch where keyWord in (select * from splitString(@keyWords, '')) END
So, how can I use a LIKE statement in the IN statement of the last query. Furthermore, I was wondering if this is the best sollution to go for. Are there any better methods? Got any tuning tips to squeeze out an extra second?
I am putting a SELECT statement together where I need to evaluate a results field, to determine how the color indicator will show on a SSRS report. I am running into a problem when I try to filter out any non-numeric values from a varchar field, using a nested CASE statement.
For example, this results field may contain values of '<1', '>=1', '1', '100', '500', '5000', etc. For one type of test, I need a value of 500 or less to be shown as a green indicator in a report, and any value over that would be flagged as a red. Another test might only allow a value of 10 or less before being flagged with a red.
This is why I setup a CASE statement for an IndicatorValue that will pass over to the report to determine the indicator color. Using CASE statements for this is easier to work with, and less taxing on the report server, if done in SQL Server instead of nested SSRS expressions, especially since a variety of tests have different result values that would be flagged as green or red.
I have a separate nested CASE statement that will handle any of the values that contain ">" or "<", so I am using the following to filter those out, and then convert it to an int value, to determine what the indicator value should be. Here is the line of the script that is erring out"
case when (RESULT not like '%<%') or (RESULT not like '%>%') then CASE WHEN (CONVERT(int, RESULT) between 0 and 500) THEN '2' ELSE '0'
The message I am getting is: Conversion failed when converting the varchar value '<1' to data type int.
I thought a "not like" statement would not include those values for converting to an int, but that does not seem to be working correctly. I did also try moving the not to show as "not RESULT like", and that did not change the message.
How I can filter out non-numeric values before converting the rest of the varchar field (RESULT) to int, so that it is only converting actual numbers?
The Lookup Transformation fails to match this datatype when full caching is enabled. When partial caching is activated (Edit > Advanced, Enable Memory Restrictions > Enable Caching) the lookup works.
I'd like to first figure out the count of how many rows are not the Current Edition have the following:
Second I'd like to be able to select the primary key of all the rows involved
Third I'd like to select all the primary keys of just the rows not in the current edition
Not really sure how to describe this without making a dataset
CREATE TABLE [Project].[TestTable1]( [TestTable1_pk] [int] IDENTITY(1,1) NOT NULL, [Source_ID] [int] NOT NULL, [Edition_fk] [int] NOT NULL, [Key1_fk] [int] NOT NULL, [Key2_fk] [int] NOT NULL,
[Code] .....
Group by fails me because I only want the groups where the Edition_fk don't match...
CREATE TABLE #Names ( ID INT IDENTITY(1,1), NAME VARCHAR(100) ) INSERT INTO #Names VALUES ('S-SQLXX') INSERT INTO #Names VALUES ('S-SQLXX.NA.SN.ORG') INSERT INTO #Names VALUES ('S-SQLYY') INSERT INTO #Names VALUES ('S-SQLYY.NA.SN.ORG') INSERT INTO #Names VALUES ('S-SQLCL-HR') INSERT INTO #Names VALUES ('S-SQLCL-MIS') SELECT * FROM #Names
--I want to filter out S-SQLXX.NA.SN.ORG because S-SQLXX.NA.SN.ORG is a duplicate of S-SQLXX eliminating .NA.SN.ORG from it.
--I want to filter out S-SQLYY.NA.SN.ORG because S-SQLYY.NA.SN.ORG is a duplicate of S-SQLYY eliminating .NA.SN.ORG from it.
--However I want to keep S-SQLCL-HR and S-SQLCL-MIS in my list of names as they do not have .NA.SN.ORG as a part of their name
--I want ONLY these returned IN the SELECT
SELECT * FROM #Names WHERE ID IN (1,3,5,6) DROP TABLE #Names
it was simple to parse simple variables using replace functions. eg. REPLACE(@str, '@customer_name', @customer_name). It worked like mail merge.the converted string was then sent forward using a webservice.now my requirement is to add conditional values in body field e.g:
body = Document ID: @document_id Customer Name: @customer_name Item name: @item_name Quantity: @qty IF isnull(@rate, 0) > 0 Rate: @rate IF isnull(@rate, 0) > 0 Amount: @amount
how can i parse strings like this. I'm open to change format of values for body field.
DelimitedSplit8k and PatternSplitLoop seem to have potential, but I'm just plain stuck on some things:
1. DelimitedSplit8k: the delimiter split the folder paths, but the pattern can be within the strings that result. 2. PatternSplitLoop: I would have to cross apply 16 times and have an awful WHERE clause to determine which of the four strings matched first.
Unless I'm missing something. Short example is below.
WITH testctes (string, pattern) AS ( SELECT 'oh_look_at_this.thing.hishers_stuffmine.craftyours_protein', 'his first' UNION ALL SELECT 'i.am.a._thing.hershis_thingsmine.refrigeratoryours_potato', 'hers first' UNION ALL SELECT 'path_like.things_minehers.some_elsehis_garbageyours_sneakers', 'mine first' UNION ALL SELECT 'more_stuff.yoursminehershis_falafel', 'yours first' ) SELECT string, pattern, ca.item, ca.itemnumber FROM testctes CROSS APPLY [dbo].[PatternSplitLoop] (string, '%his%') ca
Now I want to create usernames from #test1 by considering first character of first name and last name and if same combination found then append with 01.
Example if #test1 contains data as below:
1,Abhas, Pawar 2, Arun, Pawar 3, Ashis, Panday
Then i want to create username like:
apawar apawar01 apanday02
but if same username exists in #test2 then i want to inser records as below:
first apawar will check in #test2, if not exists insert as it is:
if apawar01 exists in #test2 then, cretae apawar02 instead of apawar01
for next create apawar03 and insert and so on...
In brief I want to check created username eith #table2 and if same exists then first check if lower value available if not then create with lower value and insert.
SELECT dbo.T1.t1_id, dbo.CONCAT(dbo.T2.product) AS product FROM dbo.T1 INNER JOIN dbo.T2 ON dbo.T1.t1_id = dbo.T2.t1_id GROUP BY dbo.T1.t1_id
but following error is be shown. why? Msg 4121, Level 16, State 1, Line 1 Cannot find either column "dbo" or the user-defined function or aggregate "dbo.CONCAT", or the name is ambiguous.
I have text column .I want to find out first occurance of string based on logic.I defiend Text with examples and also mentioned expected result.I coloured the text in word document,due to some reasons not displaying same here.Attached as image below texts to understand more clear.
TEXT 1: 'ABNAGENDRACSURENDRADJITHENDRAXNARENDRABVEERNDARAXDRMNDRAXRVINDRABNAGENDRACSURENDRADJITHEN'
From the above text1, I want to get “AXNARENDR”.
Based on logic defined below:
First I have to search for string “A” Then next to ‘A’ it should not be “B” or “C” or “D”.It can be anything other thing these three.Combination of “A” otherthan “B” or “C” or “D”
In the example text I defined “A”,”X” defined three times .I want to capture few characters from the first occurrence of the string
In t-sql 2008 r2, I would like to know how to select a specific string in a varchar(50) field. The field in question is called 'CalendarId'.
This field can contain values like:
xxIN187 13-14 W Elem HS321 13-14 D Elem IN636 13-14 C Elem 030 13-14 clark middle.
What I am looking for is the first position that contains a number value for the length of 3. Thus what I want are values that look like the following: 030, 636, 187.What I know that I want is substring(CalendarId,?,3).The question mark is where I want the starting location of a number value (0 to 9) of the value in CalendarId . I tried pathindex but my syntax did not work.
I am trying to create a stored procedure that Pulls in Chargeable and Non Chargeable hours for our employees however When I run the Stored Procedure I get this error "Conversion failed when converting date and/or time from character string." I am having a hard time figuring out were this is happening in the Stored Procedure. Also I would like to be able to Add a parameter that would be the StartDate and EndDate for which the stored procedure would pull time for.
ALTER PROCEDURE [dbo].[Chargeability] -- Add the parameters for the stored procedure here AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
I have a column containing values for different languages. I want to cut out the values per languate in a seperat column.
The syntax is a 2 letter country code followed by : the value is contained in double quotes. each languate is separated by a ; (except for the last one)
EX ur English, Dutch and Swedish:US:"Project/Prescription sale";NL:"Project/specificatie";SW:"Objektsförsäljning"
The result would Be column header US with value Project/Prescription sale
next column header NL with value Project/specificatie etc.
Here are table examples:
IF OBJECT_ID('[#SALETYPE]','U') IS NOT NULL DROP TABLE [#SALETYPE]
CREATE TABLE [#SALETYPE]( [SaleType_Id] [int] NOT NULL, [name] [nvarchar](239) NOT NULL,
SELECT CASE WHEN CHARINDEX(' ', Supervisor, CHARINDEX(' ', Supervisor, 0) + 1) > 0 THEN REPLACE(Supervisor, SUBSTRING(Supervisor, CHARINDEX(' ', Supervisor, CHARINDEX(' ', Supervisor, 0) + 1), LEN(Supervisor)), '') ELSE Supervisor END AS NewSupervisor from data d
However, I stumble when Middle Name exists somewhere in the name as Replace function repalces every occurrence of the string. For ex: "Allen, Gary G" becomes "Allen,ary"
Do we have any way to say sql to replace after certain index?
Trying to get this query to work, converting a binary version string to human readable output but somehow it doesn't work?
/* Version number binary from daily registy */ DECLARE @VERSION_STRING VARBINARY(16) = 0x4D5544532556564C5B504C552D675B; /* Inline Tally for parsing the binary string */ ;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N)) ,NUMS(N) AS (SELECT TOP(DATALENGTH(@VERSION_STRING)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2)
I have data in a trace file, and I need to extract some info such as phone number.The problem is the phone number could be varying lengths, and various positions in the row.
For example:
@City='New York', @Phone='2035551212' (10 characters, no dashes) or @City='San Francisco', @Phone='918-555-1212' (12 characters, with dashes) or @City+'Berlin', @Phone='55-123456-7890' (14 characters, with dashes)
I can use CHARINDEX to search & find @Phone=' so I know where the phone number starts, but stuck on a programatic way to find the data between the quotes since it can vary.
I am writing dynamic sql to create a work table. I would like to format my create script such that the data types are all lined up, rather than one space behind the column name. I tried something like this:
That is, I was trying to put the data type 60 spaces to the right, no matter how long the column name is (as long as it's less than 60 characters). I kept getting an error telling me it couldn't concatenate a character to an int.
Can it be possible to split the below string to NICK and SERVERS. Query which provides two columns, first column data should be NICK and other SERVERS.
I want to make a string from "TarLang.AppendText(String.Format("{1}" & vbCrLf, r("Source Language"), r("Target Language")))" and the result I get when I use that.
add the result into a new row in the datagridview
Dim CompanyID As String CompanyID = OrderNR.SelectedItem.ToString() SQL.ExecQuery(String.Format("SELECT snSrc.kod as 'Source Language', snTrg.kod as 'Target Language' FROM [teknotrans_dev].dbo.OpusOrderrow as ord INNER JOIN [teknotrans_dev].dbo.OrderVolvoLanguageName as snSrc