Transact SQL :: Search A String In A Column Which Is Enclosed By Quotations
Jul 2, 2015
I need to categorize some rows of a column into specific type. Criteria for the category search is to find a string which is enclosed at both the ends "_"
Example:
_you_,
are_you_,
are_you_jack
But when I give condition like '%_you_% I end up getting records which are like "areyou_".
create table #test
(n varchar(30))
insert into #test values
('sql_2012'),
('ssis_msbi_'),
('ssrs_msbi'),
('ssasmsbi_')
select n,
case
when n like '%_msbi_%' then 'yes'
else 'no'
end as type
from #test
In the above query I need type as "yes" only for "ssis_msbi_". But the above query results with "yes" even for ssasmsbi_. What I need to do, to get just the data which has "_msbi_" in it.
Hi I need to export data to CSV file from Sql Reporting Services. I am including single quotations in my view to display in SRS for string fields. After export if we open in Notepad, for string field it is adding multiple quotations.
How to add single quotations for string fields - CSV files,
Now my requirement is to search for FIleTYPE in above table by passing @Filename as parameter and that should return Val as response. How to write a search query for this type.
Is it possible to search a string/value across 1000's of tables and just display the table name and column name which it is in. I don't need to know every instance of the string/value only that I can find it in a given table name.
I have access to a database's web front end and a limited amount of access to the server.
I am trying to find out what column a certain string is in.
I can see some text on the front end via the web that a user enters and then submits. This information must go to the back end of the database but I dont know where it goes...
I am able to provide a unique Sequence number that it would be linked with and obviously I have the string that im looking for its column name but other then that I dont know what I can do.
I don't have write access to the database so I am unable to make procedures. I am also not really aware how the front end works or if I would be able to find the script that deals with the text after the user hits "save"...
Table A(Value), which contains some strings in the column "Value"
Table B(Key,Text), which also contains strings (in the column "Text")
Now I want to find all rows in B which contain at least one string of A and create a result table X with all found rows in B. B should contain the found keys and all found substrings for this key (separated with a comma)
The solution I am looking for may not use a Cursor and may not use the CONTAIN-Statement (fulltextsearch feature)..
SQL 2000.I need a query that will search a field in the table that is 14characters long and begins with a number.I have a client that was allowing people to store credit card numbers,plain text, in an application. I need to rip through the table andreplace every instance of credit card numbers with "x" and the last 4digits. I got the replace bit going, but I am stuck on how to searchfor a string in a field of a specific length.Any ideas?Thanks,--Mike
I have to extract a specific part of a string from a column in a sql server table. Following are the details and I have given the sample table and the sample strings.
I have 2 columns in my table [dbo].[StringExtract] (Id, MyString)
The row sample looks like the following
I have to extract the Id and a part of the column from mystring.
Id MyString 1 ABC|^~&|BNAME|CLIENT1||CLIENT1|20110609233558||BIC^A27|5014589635|K|8.1| ABC1|^~&|BNAME1|CLIENT1||CLIENT1|20110609233558||CTP^A27|5014589635|I|7.1| DEF||5148956598||||Apprised|Bfunction1||15|LMP|^^^201106101330| alloys3^ally^crimson^L||||alloys3^ally^crimson^L||||alloys3^ally^crimson^L|||||Apprised|
[Code] ....
The part I want to extract is in the line "ZZZ" and the string part that i want to extract is between the 5th and 6th pipes (|). So my output looks like the following
Id DesiredString 1 Extracts^This^String1 2 Extracts^This^String2 3 Extracts^This^String3
Is there a way to extract this either using TSQL or SSIS.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StringExtract]') AND type in (N'U')) DROP TABLE [dbo].[StringExtract] GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StringExtract]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[StringExtract]( [Id] [int] NULL,
I have a SQL text column from SP_who2 in table #SqlStatement:
like 1row shown below :
"update Panel set PanelValue=7286 where PanelFirmwareID=4 and PanelSettingID=9004000"
I want to find what table and column names are in the text ..
I tried like below ..
Select B.Statement from #sp_who2 A LEFT JOIN #SqlStatement B ON A.spid = B.spid where B.Statement IN ( SELECT T.name, C.name FROM sys.tables T JOIN sys.columns C ON T.object_id=C.object_id WHERE T.type='U' )
Something like this : find the column names and tables name
I have a scenario where in I need to use a comma delimited string as input. And search the tables with each and every string in the comma delimited string.
I am studying indexes and keys. I have a table that has a fixed width of data to be loaded in the first column which is parsed in a view based on data types within the fixed width specifications.
Example column A: (name phone house cost of house,zipcodecountystatecountry) -a view will later split this large varchar string based column b: is the source filename of the data load (varchar 256) ....
a. would there be a benefit of adding a clustered or nonclustered index (if so which/point in direction on why)
b. is there benefit of making one of these two columns a primary key (millions of records) or for adding a 3rd new column as a pk?
c. view: this parses the data in column a so it ends up looking more like "name phone house cost of house zipcode county state country" each having their own column.
-any pros/cons of adding indexes (if so which) to the view instead of the tables or both for once the data is parsed?
I'm having trouble getting the quotations right in this sql statement because of the single quotes in the displayname. It keeps breaking my application. How would you use quotes to get this to work?
Select description from lawschools_tbl where displayname='Certificat, L'Institut d'Études Politiques/'
I am trying to export a report from sql server reporting services in a csv format, but I do not want the text qualified with quotations. Is there any way to do this?
I'm using SQL 2012 express.. and just recently learned how to code.
I wrote a query and keep receiving this error...
Error converting data type varchar to float.
here's the query code
SELECT SUM(cast(lc as float)) FROM [dbo].[LaborCosts] WHERE ppty = 'ga' AND PL = 'allctd ktchn expns' AND ACCT like 'payroll%'
I am trying to sum up the values in column LC, and realized I have unnecessary quotations marks. How can I eliminate the quotations from the column, and only query the numerical values?
Hi, I have an old db with streets names and another one with employees. I have to use both tables to check if an employee adress is ok, but I have the following problem: Employee adress : 47th street nº12 2nd door b street in db 47th street
The question is how to say to sql that searchs for the employee street in the streets db cause the employee addres is bigger than any streets in db and I have no results with street like '%47th street nº12 2nd door b%' or something like that.
I have a parameter that is a varchar(50) and I want to know if there are any percent(%) signs in the string, what function can I use for this? I am looking for the T-SQL equivalent of InStr() Thanks,
Hi everybodyI have this query which need to extract names where I use the wildcard character % before and after the string to indicate that the string should not contain these characters but it is not working right .. Is there any way I could do that..SELECT name, famnameFROM inftable WHERE NAME NOT IN ('%socius%', '%rector%', '%superior%')In other words my result should not include those names that contains socius, rector, superior, this characters could begin with or in a middle of the field...thanks
Was wondering how you do the equivalent of an Oracle "Instr" function in TSQL, which returns the position of a string pattern in a specified string? I've looked through all the functions and can't find the equivalent.
Is there a T-SQL equivalent of the Oracle "Instr" function, whereby I can retrieve the position of a one specified charactier string within another? I have looked through the list of string functions and can't find an equivalent, which suprises me!
I want to search all the people in a database that names mary
I now that the sintax is something that looks like this.
SELECT Id, name FROM Socios WHERE (name LIKE '%string%') But how i input this in the vs2008 query builder for it creates an automatic searchbox toolstrip?
Hello I have a Database which contains like 1000 Tables. I am not the designer of that DB.So I need to in which table and which column that string exists. IS there a DBWIDE String search possible? Thanks and Regards
I need to search a string starting at the end to find the last space in the string, so I can determine the position of the last word in the string. Any ideas on how to do this are greatly appreciated. Kellie
I am not very familiar with MS SQL, and I need to know how to search a table for a string. I know the column that it is in, but I don't know which record. I am looking for a small piece of text in a much larger text field (we are talkinga maybe a 10k char field and I am only looking for about a 15 char string).
The 'LIKE' function looks for words that start with whatever is in the like condition. Is there an sql function similar but will look and compare at any part of the search string.
For example I am using a webservice in dot net to populate a dropdown list using this sql
SELECT compound_name FROM dbo.compound_name WHERE compound_name like @prefixText
In this table there is a compound called SILCAP310 and I would like the search function to pick up 310 if I put this into the @prefix parameter. (but I would still like the search to perform like the 'LIKE' does also.
SELECT compound_name FROM dbo.compound_name WHERE compound_name like @prefixText or compound_name SearchPartString @prefixText
I have at table that I want to update using another table which contains other values than those in the text column. table1 id: text: 1 1111, 2222, 3333 2 1234, 2222 3 0123
I want to replace the text in table1 with the new values so that it looks like below: table1 id: text: 1 aaaa, bbbb, cccc 2 qqqq, bbbb 3 oooo
My first idea was to split the text column in table1 and insert every new text with its id in a temporary table and then concaternate a new string and update the column. But how do I manage this?
I am having a colume as image datatype, in which candidates resume is stored in binary format. I have to search for particular words for eg. "ASP.Net, 2 Years" into that column I have converted that column as varchar and then searching above two words(after spliting them by comma) into that string using CHARINDEX
DECLARE @i int, @j int SET @i = select MIN(ResumeId) from table_name SET @j = select MAX(ResumeId) from table_name
WHILE (@i <= @j) BEGIN DECLARE @Resume varchar(max) SET @Resume = CAST((select Resume from table_name where ResumeId=100) as varbinary(max))
--code to search the particular word from @Resume
END
It is working fine, but it is very slow, taking 2+ minutes to scan 15000 records How can I make it faster or is there any other way to search?
I am using a web service to search for users by either their name or their nickname. Users can type the persons name into a dot net text box that has a Autocomplete extender - which means it returns a list of user with each keystroke.
This works fine if I just search for a name.
select r.fname +' ' + r.lname from users u join log l on l.riderid = u.riderid
where fname +' '+ lname like @item +'%' group by l.fname , r.lname, nicknamellame
So if I am searching for Dan Brown When I enter a D I get all users whose name starts with D, A reduces the list to those that start with Da until you get a small enough list to find Dan Brown. This works - what I would like to allow is if they entered the users nickname (another column in the users table) it would also include those options: So, if Dan's nickname is DannyBoy typing that in would return the nickname.
select r.fname +' ' + r.lname , nickname from users u join log m on l.riderid = u.riderid where (fname +' '+ lname like @item +'%') or (nickname like @item + '%') group by r.fname , r.lname, r.nickname having max(l.[date]) >= '2014-01-01'
