Alright, I am trying to write a query that will select a column.. then search through the column and return me the fields with no underscores in them.
i.e we have fields that are like ABCD and ABCD_1. I want to basically return all of them up to the underscore.
so i am trying to use a substring combined with patindex. Here is my statement for patindex. patindex('%_%', short_name)
however, i cannot get patindex to ignore the fact that _ is a wildcard and treat it as character. Ive tried ('%[_]%', short_name) and ive tried ('%!_%', short_name) escape '!' and i just keep getting errors... If someone could help me out, that would be most appreciated. thank you.
I would like solving the following issue using the Patindex function i cannot retrieve or extract the single numeric value as an example in the the values below i would like retrieve the Value 2, but in my result set the value 22 also appears or it is completely omitted.
I was wondering if someone would point out my flaw. I am attempting to get information separated by spaces, placed into separate columns. I tried without success several times yesterday to get what I desired and was not able to get it done. I am fairly new at developing SQL code and am low on the SQL coding totem pole of experience but would like your help.
My problem arises when I, in the second column, attempt to get only the information BETWEEN the first space: PATINDEX('% %', c1)+1 and the second space. I am coming up short and would like someone to point out my error or assist me in getting the query pointed in the right direction.
hi! i have one datatable with some file's details like id, name, createdate. i wrote one storedprocedure to search in that table by filename using wildcard character. its also working perfectly. But the problem is i want to arrange the results in ascending order depended by matching level. If I search for 'file123' it should give results in one order by matching level, means - lets think the results are 'fil23', file123', 'file', 'file1'. the results should be in order like 'file123', 'fil23', 'file1', 'file'. the results of the sp which i used, are order by datecreated. Please help me to do this.
the storeprocedure i used - "CREATE PROCEDURE [dbo].[SP_DYN_SearchinFiles_DynByWildcardsADMIN] @searchparam nvarchar(50) AS BEGIN select Id, OriginalName, ContentType, FullName, Department, DateCreated, Client, username from Files_Dyn where PATINDEX('%' + @SearchParam + '%', OriginalName) > 0
Hello,I am trying to update a column in a table with an 8 digit string.The 8 digit number is obtained from another column which is a commentsfield.e.g.Comments Field :1) RD-V0216970 RQST NON SMKING KING Unknown # : 223389212) Received verbal authorization Authorization # 0403453) international plaza, singapore # 96722540The code that I am using isUPDATE U SET U.NUM =CASEWHEN U.BOOKED_COMMENTS_TXT LIKE('%[0123456789][0123456789][0123456789][0123456789][0123456789][0123456789]%')THENSUBSTRING(U.BOOKED_COMMENTS_TXT,PATINDEX('%[0123456789][0123456789][0123456789][0123456789][0123456789][0123456789]%',U.BOOKED_COMMENTS_TXT), 8)ELSE NULLENDFROM UNKNOWN1_RESERVATIONS UHere's what my result set looks like1)V02169702)0403453)96722540But this is how I want my result set to look like1)223389212)null3)96722540What I need is a way to restrict the search criteria to exactly 8numeric digits. Any suggestions will be helpful.Thanks in advance
Hi allUsing SQL Server 2000 SP3 on WIn2K Pro with SP2.When I do thisSELECT PATINDEX('%[%', 'ABC[DEF')GOI get a result of 0, when I would expect a result of 4.This works as I expect:SELECT PATINDEX('%]%', 'ABC]DEF')GOI must be missing something really obvious here Any help would beappreciated.
Hi all, My database is not case-sensitive, but I want output like... SELECT patindex('%[A-Z]%','gaurang Ahmedabad') The output should be first occurrence of uppercase A to Z, so output should be 9 it should not be 1. Above query is giving output as 1 bcoz the 1st character in the expression is 'g' and it is in A to Z, but this is not capital 'G'. The 1st capital letter in the expression is 'A' (9th character in the expression). Is there anyway to achieve this using PATINDEX? or Is there any other way to achieve this? Thanks, Gaurang Majithiya
I have a number of columns I'm selecting from a table, and I'd like to use PATINDEX to search for a string within a field. Upon finding this string it would return another string...:
SELECT CASE PARTNERID WHEN PATINDEX('%INX%',PARTNERID)>0 THEN 'AXXENT' ELSE PARTNERID END AS PARTNERID
However, I'm getting this error:Incorrect syntax near '>'.
I have a table which stores a big chunk of html text, which I have to search for and replace some strings. How can I write a query that makes use of regular expressions to do the following:
Below is a block of example text ====================================================================== " src="http://aaaa/bbbb/cccc.nsf/blind%2010x10.gif?OpenImageResource" width=10> <img height=10 alt="" src="http://aaaa/bbbb/cccc.nsf/blind%2010x10.gif?OpenImageResource" width=10><img height=11 alt="" http://aaaa/bbbb/cccc.nsf/blablablabla?OpenDocument src="http://aaaa/bbbb/cccc.ns http://aaaa/bbbb/cccc.nsf/d8d4f0dfa09ba71142256cc50040ee51/ee9b4493dac727be422571c30027df61? OpenDocument" target=_self>
Now I want to search and replace the string that is highlighted in red with something like "FOUND" The below string is what I want to search for - I will used PATINDEX on the string below to find the start index of the string in the body, and then I will add on 114 to the when doing a replace:
what would the regular expression be and how does one type it in the query? I tried something like the below but it does not work, please advise ..... [a-z][0-9]{32} %/% [a-z][0-9]{32}
I have a value in a column '01-08-087-0101W5'. I would like to break this value down into 5 different columns. The column will be broken down at the dashes , so the 5 columns will have values like
01 08 087 101 5
I would also like to trim any leading 0's in the above values. i have been trying the following
SUBSTRING(column1,patindex('%[^0]%',column1) ,2) AS FROM_TWP
I am creating a view to pull data for a UPS integration I am doing. I currently have this view where I pull my data from. All is well accept for my PATINDEX,
Currently I have this PATINDEX So when a user puts this into delivery instructions "#999999" UPS_FINAL returns 999999 which is good for me to use. But The PATINDEX will also grab all kinds of neat stuff out of the delivery instructions field when I really only want whatever is after the # sign in the field.
I am new to sql and dont quite understand how this search is working but I think I am in need of a better way to search the field
Code:
WHEN SUBSTRING(P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions, PATINDEX('%[^a-z ]%', P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions) + 1, 6) IS NULL
THEN dbo.Address_Table.ups_code
ELSE SUBSTRING(P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions, PATINDEX('%[^a-z ]%', P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions) + 1, 6) END AS UPS_FINAL
------------------------------------------------------------------------------------- What I am looking to accomplish is a SUBSTRING that will search delivery_instructions
Code:
SELECT
TOP (100) PERCENT P21PLAY.dbo.p21_view_oe_pick_ticket.pick_ticket_no, P21PLAY.dbo.p21_view_oe_hdr.order_no, P21PLAY.dbo.p21_view_oe_hdr.customer_id, P21PLAY.dbo.p21_view_oe_hdr.ship2_name, P21PLAY.dbo.p21_view_oe_hdr.ship2_add1, P21PLAY.dbo.p21_view_oe_hdr.ship2_add2, P21PLAY.dbo.p21_view_oe_hdr.ship2_city, P21PLAY.dbo.p21_view_oe_hdr.ship2_state, P21PLAY.dbo.p21_view_oe_hdr.ship2_zip, P21PLAY.dbo.p21_view_oe_hdr.po_no, P21PLAY.dbo.p21_view_oe_pick_ticket.carrier_id AS Carrier, P21PLAY.dbo.p21_view_oe_pick_ticket.carrier_id AS Supplier,
P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions) + 1, 6) AS UPS_Shipper,
------------------Checks view for email address or assigns alternate------------------ (CASE WHEN charindex('@', p21_view_contacts.email_address) > 0 THEN p21_view_contacts.email_address ELSE
'email@domain.com' END) AS alternate_address,
'Y' AS QVN, 'email@domain.com' AS failureaddress,
P21PLAY.dbo.p21_view_contacts.email_address,
------------When carrier_id is not one of these # then Null; else ------------------------------
CASE WHEN P21PLAY.dbo.p21_view_oe_pick_ticket.carrier_id NOT IN (105188, 105191, 105194, 105197, 105200,
105203, 105206, 105209, 105212) THEN NULL
----------------- Looks for special Character in delivery_instructions; if NULL then ups_code; ELSE return value from delivery_instructions as UPS_Final--------------------
WHEN SUBSTRING(P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions, PATINDEX('%[^a-z ]%', P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions) + 1, 6) IS NULL THEN dbo.Address_Table.ups_code ELSE SUBSTRING(P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions, PATINDEX('%[^a-z ]%', P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions) + 1, 6) END AS UPS_FINAL
FROM dbo.Address_Table INNER JOIN P21PLAY.dbo.p21_view_oe_pick_ticket INNER JOIN P21PLAY.dbo.p21_view_oe_hdr ON P21PLAY.dbo.p21_view_oe_pick_ticket.order_no =
P21PLAY.dbo.p21_view_oe_hdr.order_no ON dbo.Address_Table.id = P21PLAY.dbo.p21_view_oe_hdr.customer_id LEFT OUTER JOIN P21PLAY.dbo.p21_view_contacts ON P21PLAY.dbo.p21_view_oe_hdr.contact_id = P21PLAY.dbo.p21_view_contacts.id
WHERE (P21PLAY.dbo.p21_view_oe_hdr.completed <> 'Y') AND (P21PLAY.dbo.p21_view_oe_hdr.delete_flag <> 'Y') AND (P21PLAY.dbo.p21_view_oe_hdr.will_call <> 'Y') ORDER BY P21PLAY.dbo.p21_view_oe_pick_ticket.pick_ticket_no
---Checks that input only contains numbers if PatIndex('%[^0-9]%','11') > 0 Begin Print 'Not all numbers' End Else Begin Print 'All numbers' End
---Checks that input only contains letters if PatIndex('%[^a-z]%','aaaaa') > 0 Begin Print 'Not all letters' End Else Begin Print 'All letters' End
--Checking for mixed input If PatIndex('%[^0-9][0-9]%','abc') > 0 Begin Print 'Alpha numeric data' End Else Begin Print 'Either all numbers or all letters' End
--Checks that value must start with a letter and a number If PatIndex('[^0-9][0-9]%','A1anamwar11') > 0 Begin Print 'Starts with a letter and a number' End Else Begin Print 'Does not start with a letter and a number' End
--Checks that value must End with a letter and a number If PatIndex('%[^0-9][0-9]','A1anamwar11a1') > 0 Begin Print 'Ends with a letter and a number' End Else Begin Print 'Does not End with a letter and a number' End
--Checks that value must Start with a letter and Ends with a number If PatIndex('[^0-9]%[0-9]','namwar1') > 0 Begin Print 'Starts with a letter and ends with a number' End Else Begin Print 'Does not start with a letter and ends with a number' End
Hello, I understand that it is not possible to use PATINDEX in a Derived Column transform. I'm trying to eliminate leading zeros from a column where the string is always 14 characters long, but the first non zero character could occur any number of characters from the left of the string. The following achieves what I need:
substring([Account No w/0s], patindex('%[^0]%', [Account No w/0s]), 14) AS ExtractedAcctNo
Does anyone happen to know how I might express this differently in a Derived Column transform? Should I consider a calling a function through an OLE DB Command transform on each row instead? Maybe I should have a SQL Task that runs an UPDATE statement against the column.
My database is not case-sensitive, but I want output like...
SELECT patindex('%[A-Z]%','gaurang Ahmedabad')
The output should be first occurrence of uppercase A to Z, so output should be 9 it should not be 1.
Above query is giving output as 1 bcoz the 1st character in the expression is 'g' and it is in A to Z, but this is not capital 'G'. The 1st capital letter in the expression is 'A' (9th character in the expression).
Is there anyway to achieve this using PATINDEX? or Is there any other way to achieve this?
My server is a dual AMD x64 2.19 GHz with 8 GB RAM running under Windows Server 2003 Enterprise Edition with service pack 1 installed. We have SQL 2000 32-bit Enterprise installed in the default instance. AWE is enabled using Dynamically configured SQL Server memory with 6215 MB minimum memory and 6656 maximum memory settings.
I have now installed, side-by-side, SQL Server 2005 Enterprise Edition in a separate named instance. Everything is running fine but I believe SQL Server2005 could run faster and need to ensure I am giving it plenty of resources. I realize AWE is not needed with SQL Server 2005 and I have seen suggestions to grant the SQL Server account the 'lock pages in memory' rights. This box only runs the SQL 2000 and SQL 2005 server databases and I would like to ensure, if possible, that each is splitting the available memory equally, at least until we can retire SQL Server 2000 next year. Any suggestions?
We have an old machine which holds SQL server 2000 database. We need to migrate a whole database to a new machine which has SQL server 2005.
When we tried to move whole database using Import and Export Wizard, only tables can be selected to import/export. However we want to import/export the whole database, including tables, stored procedure, view, etc. Which tool should we use?
We have an old machine which holds SQL server 2000 database. We need to migrate a whole database to a new machine which has SQL server 2005.
When we tried to move whole database using Import and Export Wizard, only tables can be selected to import/export. However we want to import/export the whole database, including tables, stored procedure, view, etc. Which tool should we use?
When I proposed start to use SQL Server 2005 for new VS 2005 web sites, one of my co-workers responded that we will update the old SQL Server 2000 databases to SQL Server 2005 when we are ready to use 2005 SQL Server.
Questions: 1. Any expected problems to upgrade old 2000 databases to new 2005 SQL Server? 2. I have installed both 2005/Management Studio Express and 2000/Enterprise Manager in my PC. Any expected problems when running both 2000 and 2005 SQL Server at the same database server? 3. What is the best configuration for running SQL Server 2005 when we have old 2000 databases? Upgade or not upgrade?
I am getteing need help Query analyzer error Unable to connect server local Msg17, level 16,state 1 ODBC SQL server driver [DBNETLIB]SQL server does not exist
Hi, I am having a problem connecting my .net applications from the application server to the database server. When I run the application from my windows xp (sp2) box it works fine. When I try to connect via SQL Management Studio to the database server from the application server I get the same error. Here is the error: System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) Here is the Environment: App Server: Windows Server 2003 Standard Edition Inside Company's Firewall/ Network Database Server: Windows Server 2000 Advanced Edition SQL Server 2000 SP4 Remote Connections to the Server is checked Enable Protocols: Named Pipes & TCP/IP TCP/IP Port: 1402 (I don't know why it isn't the default of 1433) The db server is sitting out side the Company's firewall (don't ask me why). I can access it fine from inside the firewall on my XP box but not from windows server 2003. There is a web server outside the our network that also connects to the db server with no problem and that is running Windows Server 2003 Web Edition. I can ping the db server from the app server using the IP address. I tried using the IP address and the port 1402 in my connection string but that didn't work from any machine (XP and Server). I imagine the issue is somehow related to the company's firewall but why would it only block Windows Server 2003 and not XP? What do I tell the network admin to change? Any help would be appreciated. Thanks, Oran
if you can restore a database to Server B using Server A as the service. Meaning we would issue the command on Server A but somehow point to Server B as where we want the restore to happen.
The backup file would be in a location independent of both servers.
Dear all,On Win2000 server with SP3, I am trying to access a SQL Server 7.0database, "TestDB", from VB6 via a SQL Server ODBC system DSN using ADO2.7. In SQL Server Enterprise Manager, there is a login named "Tester".In its property window, NO "Server Roles" was assigned but its"Database Access" was set to "TestDB". This login was also made as theuser of "TestDB" with "public", "db_datareader" and "db_datawriter"selected as its "Database role membership". All the tables I am tryingto access in "TestDB" were created under "Tester".My code is like:Set conn = New ADODB.Connectionconn.Open "DSN=TestDSN;UID=Tester;PWD=test"Set cmd = New ADODB.Commandcmd.ActiveConnection = conncmd.CommandText = SQLset rs = cmd.Execute()If I set the SQL to something like "SELECT * FROM tbl_test", I alwaysget an error of "-2147217865" saying "[Microsoft][ODBC SQL ServerDriver][SQL Server] Invalid object name tbl_test". If I set the SQL to"SELECT * FROM Tester.tbl_test", everything runs properly. Could anyoneplease kindly advise why the first SQL is not working? Or in otherwords, why must I prefix the table name with its owner while the DBconnection is already made under that owner name? Thanks in advance.Tracy
When I try to migrate a database on a SQL Server 2000 server to a SQL Server 2005 server with the Copy Database Wizard of the SQL Server Management Studio, I'm confronted with the following problem;
Performing operation...
- Add log for package (Success) - Add task for transferring database objects (Success) - Create package (Success) - Start SQL Server Agent Job (Success) - Execute SQL Server Agent Job (Error) Messages * The job failed. Check the event log on the destination server for details. (Copy Database Wizard)
When I take a look at 'Event viewer' on the SQL 2005 server, the following error is displayed;
InnerException-->An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
I already enabled the MSSQLSERVER network configuration protocols (TCP/IP and Named Pipes ).
My site works fine in VWD2008 express, but I get this error when I try to use it on my live website. An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. According to this article: http://support.microsoft.com/kb/914277 I am supposed to:
1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration. Ok, there is no such program in this folder. The only thing in there is "SQL Server Error and Usage Reporting"... The other thing I am greatly concerned with is this: All is want is for my webpages to be able to access my database for user authentication. I DO NOT want to grant the internet rights to remote connect to my database.
Hi All, Please some one help me... I have to insert a csv into one table in sql server. But the problem is the file is in one server and SQL SERVER 2005 is in other server.. how do i insert the file.... please help me.....
