I had a User Management module in my application where I created a user with name
`~!@#$@%^&*()[_]+|}{":?><-=[[]];',./
Now I have a functionality to search for the user existing. For that give the search string or a single character and it finds out all the records containing the character.
How do I go about it as the SP i created for it gives correct results except the following
1. Search for % - Gives all record
2. Search for _ - Gives all records
3. Search for [ - Gives NO record
4. Search for the whole string - Gives NO Record
everytime i want to query for any of tbl_c_? that contain a specific value i have to reference all 25 in my query. is there a better way? I cannot change the table.
Hi guys, I have a gridview and textbox. I can filter the grid just fine with the textbox, but I would like to allow the user to type in part of a name. I thought something like this would work but it doesn't: Emp_Name LIKE '%'+@Emp_Name+'%' Thank you.
I am developing for a customer and they want a search facility that uses boolean logic and special characters. So they want to be able to add "AND" "OR" "NOT" "*" and "?". And for this to effect the search in the predicted way and ranked. I was wondering if there is any examples of this type of search implemented?Â
i am using oledb to connect to oracle. i want to know if there is a way to handle different character sets in this type of connection. for sql to sql, i have been using auto translate in the connection string. what about for sql oledb to oracle? how can i make sure that the data from sql to oracle is transferred as is?
My requirement is that if the string in the column has any of the characters from 'ACDIPFJZ' , those characters have to be retained and the rest of the characters have to be removed.
I want to update data only where the value of the 'image_path' column is NOT = 192.168.150.12/images/*
Im basically trying to exclude creating duplicates, where this path already exists.
Here is my code:
INSERT INTO IMAGE (FCN, IMAGE_NAME2) SELECT FCN, Col066 FROM GRAB where Col066 <> ' ' update IMAGE Set PERIMAGE_PATH = 'http://192.168.150.12/images/' +IMAGE_NAME2+ '.jpg' FROM IMAGE WHERE image_name2 IS NOT NULL and perimage_path is NOT = 192.168.150.12/images/*
What is the proper code to do this. I know the last line does not work. Thanks
I have a problem with SQL CE 3.5 and VS 2005. When I execute query with parameters and wildcard, I have an error : FormatException. Here is my query with parameters : Query: SELECT "PARAGRAPHES"."PARA_ID" AS "Numero","PARAGRAPHES"."PARA_SECT_ID" AS "Section","PARAGRAPHES"."PARA_NUMERO" AS "Rang","PARAGRAPHES"."PARA_GROUPE" AS "Groupe","PARAGRAPHES"."PARA_ENTITES" AS "Entites","PARAGRAPHES"."PARA_LBL_COURT" AS "Court","PARAGRAPHES"."PARA_LBL_LONG" AS "Long","PARAGRAPHES"."PARA_ACTIVE" AS "Active","PARAGRAPHES"."PARA_CONDITIONS" AS "Conditions","PARAGRAPHES"."PARA_PARENTS" AS "Parents","PARAGRAPHES"."PARA_QUESTIONS" AS "QuestionsConditions" FROM "PARAGRAPHES" WHERE ( ( ( ( "PARAGRAPHES"."PARA_SECT_ID" = ? AND ( ( "PARAGRAPHES"."PARA_ENTITES" LIKE ?) OR "PARAGRAPHES"."PARA_ENTITES" IS NULL))) AND "PARAGRAPHES"."PARA_ID" IN (SELECT "DOSSIER_LINKS"."DOLI_PARA_ID" AS "Paragraphe" FROM "DOSSIER_LINKS" WHERE "DOSSIER_LINKS"."DOLI_DOSS_ID" = ?) AND "PARAGRAPHES"."PARA_NUMERO" = ?)) Parameter: @Section1 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 52. Parameter: @Entites2 : String. Length: 3. Precision: 0. Scale: 0. Direction: Input. Value: "%D%". Parameter: @Dossier3 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1. Parameter: @Rang4 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 2. and my error : [System.FormatException] = {"@Entites2 : %D% - FormatException"}
If I execute query in Query Analyzer (without parameter : values directly on query) there is no errors.
I just migrated a stand alone MSAccess2003 .mdb to an .adp Access Data Project. In the former .mdb I had a parameter query to search for a portion of a 17 character part number, primarily the 2 grouping of 4 digits as in (00-0000-XXXX-0-00). In Access the query was written as
LIKE “*”&[Enter a portion of the part number]&”*”
Then we ported to a web based portal using MSAccess’s DAP (data access pages) and I was told due to ADO vs DAO the query had to be rewritten using the % symbol as in
LIKE “%”&[Enter a portion of the part number]&”%”
So far so good. However with the migration to a SQL Server and ANSI-92 compatible wildcard characters this has changed. The new query looks like this
LIKE @Enter_a_portion_of_the_part_number +n’%’
The problem I am having is the prompt for data entry works but the wildcard will only return matching values from the first digit place holder. Some of the books I have read said to use the Under Score character to move the search criteria to the section I want to search which would be starting with the 9th character (00-0000-XXXX-0-00) as in
LIKE @Enter_a_portion_of_the_part_number +n’________%
However the Under Score only works with Alpha Characters and not Numeric.
So how do I write a SQL Server Query to perform like it did in Access?
Hi, I'm working with a third party app on SQL Server 2000, and from what I can gather, programmed in C# & VisualFoxPro.
When we search with Note contains 94949 we get 571 results, when we search with Note contains 94949* we get 575 results.
There should be at least a hundred different entries that start with "94949-1" so I expected the query with the wildcard to return something like 680 results, not an additional four rows.
I'm a bit embarrassed about this question, because I'm sure that a lot of you would find it trivial, but I'm really not the best at T-SQL and especially not string handling.
I'm trying to generate a 'parent' value, by replacing characters in the 'child' field with '---'. It's a classic Chart of Accounts sort of problem, for feeding into a Parent/Child dimension in AS. It's a lot easier to understand if you look at this:
That is to say, 0.0.0.---- is the parent of 0.0.0.5001 0.0.-.---- is the parent of 0.0.0.---- 0.-.-.---- is the parent of 0.0.-.----
So, what I really need to do is replace the last non '-' string with '-'. I've tried various combos of PATINDEX, CHARINDEX, REPLACE etc, but I'm really struggling. To make in more complicated, and of the strings between the . can be any length.
Has anyone seen a problem querying Excel or Access database when the "LIKE" clause is used with the "*" wildcard? The problem I'm seeing is that the query fails to return a dataset whenever "*" wildcard is used.
For Example,
sQry="Select * From [EmployeeData$] WHERE Id LIKE 'AAA'" 'query WORKS
sQry="Select * From [EmployeeData$] WHERE Id LIKE 'AAA*'" 'query does NOT WORK
Assume that Table name is "EmployeeData", with "ID", "Name" and "Birthdate" as Fields.
Data: ID Name Birthdate
AAA Aaron 5/4/1975
CCC Charlie 10/14/1948
DDD Deloris 7/19/1998
The code I use is listed as follows:
Imports System.Data.OleDb
Imports System.Data Public Class Form1
Private m_sConn1 As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:ExcelData1.xls;" & _
"Extended Properties=""Excel 8.0;HDR=YES"""
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
'Use a DataSet to Query data from the EmployeeData table.
Dim QryConn As New OleDbConnection(m_sConn1)
'Dim strQry as string ="Select * From [EmployeeData$] WHERE Id LIKE 'AAA'" '- works
Dim strQry as string ="Select * From [EmployeeData$] WHERE Id LIKE 'AA*'" ' does not work
Dim da As New OleDbDataAdapter(strQry, QryConn)
Dim ds As DataSet = New DataSet
da.Fill(ds)
Dim dr As DataRow
For Each dr In ds.Tables(0).Rows 'Show results in output window
You get the idea. So logproc just logs that the procedure has started or stopped to a table so we can monitor it easily.
The procedure is then run by an agent job every morning. This morning we had a little bit of an odd one. In the example above we effectively got to 'proc3' being started (as it was logged). However there was then an error of String or binary data would be truncated (severity 16 i believe). However when proc3 was then manually run it worked (the data was unchanged). Then going back proc2 works fine, and its actually proc1 that has the error.
I have looked through the procs in question (and the wrapper) and cant find any error handling that is relevent (there one try and catch block completely separate at the end of the wrapper procedure for a small routine).
I am using nvarchar(MAX) string variable. But its length is maximum upto 8,000 charaters. But I want to assign 10,000 characters. So how can I get this.
I am trying to select the last 3 characters from a string. I am running into problems because the sting that I am selecting from are not the same amout of characters.
For example:
Item
abc145264 efg1254 wqx21456
How would I be able to select the last three characters from a list that could have more than 50 variations on the number of characters.
I tryed right(item, 3) but that does not work because all the lenghts are different. Any ideas?
I have a phone number string (416) 555-5555 in a table. I'd like to perform a search on the string so that the user is able to pass any number, and the query returns all phone numbers like it. What I'd like to do is to strip out the brackets and dashes and perform a like search.
HelloI want to write a stored procedure (using Enterprise Manager) that can grabthe digits that are inbetween the two dashes (-) in strings like:123-150-401-123-832-4215-61The digits to the left, right and inbetween the dashes could be any length,so a static "get the 5th, 6th and 7th digit" stored procedure won't work.Many thanks,--Chris Michaelwww.INTOmobiles.comDownload 100s of ringtones, wallpapers & logos every month for only £1.50per week
I'm trying to search for commonly abbreviated company titles (ie limited, partnership, and so on). I would like to make my sql statement as short as possible (it's already quite lengthy as is). But I'm having trouble netting the abbreviated forms such as LTD and LMTD for limited (I have no control over the data I get, it comes from different counties with no standardization). I've tried using braketted strings like "L[I,IMI,M,]T[ED,D,]" and all other combinations I can think of, including using single quotes in the each string, and removing the empty placeholder and still can grab all instances.
Ok so we gotSELECT this, that, others FROM some.database WHERE this=@this So in the database the others field is a string that can have up to 200 characters, but on this particular data pull I only want to pull the first 50 characters of the others field. How can I do that? Thanks.
I was wondering what would be the best way to remove special characters like, '-', '&' '(',')','#','*', etc... from a number string. To be specific a phone Number string where the string is >= 10.
As part of a data search project I need to be able to strip all non numeric characters from a text field. The field contains various forms of phone number in various formats. In order to search on it I am going to remove all non numeric characters from the input criteria and from the data being searched.
In order to do this I decided on using a SQL Server custom function: Pass in field. Loop through all chars, test against asci values for number range. return only numernic data concatenated into a string.
Are there any other more efficient ways of going about this?
I need extracting string that is between certain characters that are in certain position.
Here is the DDL:
DROP TABLE [dbo].[StoreNumberTest] CREATE TABLE [dbo].[StoreNumberTest]( [StoreNumber] [varchar](50) NULL, [StoreNumberParsed] [varchar](50) NULL) INSERT INTO [dbo].[StoreNumberTest]
[Code] ....
What I need to accomplish is to extract the string that is between the third and fifth '-' (dash) and insert it into the StoreNumberParsed while eliminating the fourth dash.
Sample output would be:
KY117 CA132 OH174 MD163 FL191
I know that parse, charindex, patindex all might come in play, but not sure how to construct the statement.
Y'all:I am needing some way, in the SQL Server dialect of SQL, to escape unicodecode points that are embedded within an nvarchar string in a SQL script,e.g. in Java I can do:String str = "This is au1245 test.";in Oracle's SQL dialect, it appears that I can accomplish the same thing:INSERT INTO TEST_TABLE (TEST_COLUMN) VALUES ('This is a1245 test.");I've googled and researched through the MSDN, and haven't discovered asimilar construct in SQL Server. I am already aware of the UNISTR()function, and the NCHAR() function, but those aren't going to work well ifthere are more than a few international characters embedded within astring.Does anyone have a better suggestion?Thanks muchly!GRB-----------------------------------------------------------------------Greg R. Broderick Join Bytes!A. Top posters.Q. What is the most annoying thing on Usenet?---------------------------------------------------------------------
MS SQL 2000. Does anyone know how to find all rows where an nvarchar column contains a specific unicode character? Is it possible without creating a user defined function? Here's the issue. I have a table Expression (ExpID, ExpText) with values like 'x < 100' and 'y ≤ 200'. where the second example contains Unicode character 8804 [that is, nchar(8804)]. Because it's unicode, I don't seem to be able to search for it with LIKE or PATINDEX. These fail: SELECT * FROM Expression WHERE ExpText LIKE '%≤%' -- no recordsSELECT * FROM Expression WHERE PATINDEX('%≤%', ExpText) -- no records However, SELECT PATINDEX('%≤%', 'y ≤ 200') will return 3. Any suggestions? Thanks in advance.
