Hi Everyone- i woder if someone know Built in function to search the occurence of char in string in T-SQL
and iam not talking about the Index of the first occurence (e.g CHARINDEX ) but it is more generic
i want to send to the function to ask about the index the second or the third or N occurence of the char. and i wonder if it is built in for performance reasons
example getseachIndex("M|d|d","|",2)==> the result is 4
I have a text field in a table that contains number along with chars.Is there a way i can write a query to show all the fields that containsjust Numbers or Char in a field??TBALE ExampleCOL1 : COL2(nvarchar)---------------------------100 345G01200 123456789300 GQ9220
Hi guys, first post here. It's been a few years since I've worked with ODBC, so I'm a bit rusty. I'm having trouble figuring out how to use a char* string as a parameter for a SELECT statement. I'm trying to compare the contents of a varchar column (with size=20) against the string.
When SQLFetch() runs, it returns 100 (SQL_NO_DATA). If I type the query manually into my Enterprise Manager console, I get several results, so I know the query is correct. Any idea what I might be doing wrong with the ODBC calls?
I am attempting to find quotes (") in a column and replace with the string '--THIS-WAS-QUOTES--'. Right now my script only converts the first quote it finds in the description column, converts to the string and moves to the next row leaving the other quotes as they were. Below is my query script
I am attempting to find quotes (") in a column and replace with the string '--THIS-WAS-QUOTES--'. Right now my script only converts the first quote it finds in the description column, converts to the string and moves to the next row leaving the other quotes as they were. Below is my query script
#1 This stored procedure can be used to search and replace substring in the char, nchar, varchar and nvarchar columns in all tables in the current database. You should pass the text value to search and the text value to replace. So, to replace all char, nchar, varchar and nvarchar columns which contain the substring 'John' with the substring 'Bill', you can use the following (in comparison with the SetTbColValues stored procedure, this stored procedure replace only substring, not the entire column's value):
I have a table where this one column(varchar) has rows of data with a period at the end of the string. Is there any function I could use to remove all the periods?
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 have used a query statement with the following WHERE string to 'Fill' a dataset. "AND (A.ApptsDate > '" & strApptPreDate & "' OR (A.ApptsDate = '" & strApptPreDate & "' AND A.ApptsTime >= '" & strApptPreTime & "' ))" & _ and strApptPreTime is defined as:Dim strApptPreTime As String = SomaShared.strPadTime(CStr(dApptCalcNewDate.Hour) + ":" + CStr(dApptCalcNewDate.Minute))
Somehow, the dataset showed only the ApptsTime after 10 am. After more than 2 weeks of debugging, I still can see a dataset watch for > 10 amAppsTime only. Now I am guessting, the problem is 9 is different from 10 - 16, it all because 9 is single digit. Then I check the data type settings for these variables. Here are what I found: In SQL Server Agent job, the ApptsTime data was 'inserted' by @NewApptTimes, which is declared as char(5).In SQL Server database, the ApptsTime was defined as nvarchar(15). My question are: 1. The reason why there were no 9 am data for the dataset, is becasue 9 am of nvarchar(15) is not > 8:30 of strApptPreTime? 2. If the answer to the quation 1 is yes, how do I define AppsTime and/or strApptPreTime? TIA,Jeffrey
I've been trying to return hex data in a way that can be concatenated. I need the actual hex info (e.g. 0x6E3C070) as displayed since it contains info about the path to a file. So I can turn it into D:6E3C 7 to get the path to the file. In searching around I have come across a way to do this but can't figure out how to get it to run through a column and either display or insert into a table multiple results.
-Here's the user function that converts an integer into a hex string-
CREATE FUNCTION udf_hex_string (@i int) RETURNS varchar(30) AS BEGIN DECLARE @vb varbinary(8) SET @vb = CONVERT(varbinary(8),@i) DECLARE @hx varchar(30) EXEC master..xp_varbintohexstr @vb, @hx OUT RETURN @hx END GO
--'PageStoreId' contains the data that needs to be converted into the editable hex string --'HexString' is where I'd like it to go so I can parse it later.
--I can run the below select and get the hex string. But am stuck on how to run a select or update that would run through the 'XPages.PagestoreId' column and insert the hex string into the 'XPages.Hexstring' column. 'XPages.PagestoreId' could have 100's of entries that need to be converted and placed in the relevant the 'XPages.Hexstring' column.
Hi, What is the difference updating a null value to char/varchar type column
versus empty string to char/varchar type column?Which is the best to do and why? Could anyone explain about this?
Example:
Table 1 : tCountry - Name varchar(80) nullable Table 2 :tState - Name char(2) nullable Table 3 :tCountryDetails - countryid,state (char(2) nullable) - May the country contain state or no state So,when the state is not present for the country ,i have two options may be - null,'' tCountryDetails.State = '' or tCountryDetails.State = null?
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).
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"...
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?
Hi, I have a SQL server 2005 database with a series of multiple fields. One of the fields has a array of strings seperated by semi-colons like so: Red;Green;Blue My question is, how can i run a query on all of the fields that have the value of say Green in it. Note that these values vary in different order and numbers. Thanks
I am pretty new to SQL server 2005 and have the following issue: I get the date from the asp applciation in format dd/mm/yyyy and then try to do a search for all records that might match that date in sql server 2005. The date is storred in ther database in datetime format. I am having trouble composing the stored procedure to do that. Any help would be apreciated. Sincerely d
