How to get valid value from the following query SELECT Answer FROM table WHERE values LIKE '%[^0-9]%' Basically I want the data can deal for 28,000 (valid) $20000 (valid) Annual Amount (invalid) ? (invalid) 28.00 (valid) Thanks ...
Hi, I'm building a website using ASP.NET and SQL Server, and I use SELECT PK FROM Table WHERE PK = @@identity My question is which is better and more reliable to retrieve the last inserted PK for multiuser website, using @@identity or using this: SELECT MAX(PK) FROM Table WHERE PK = Session ("UserID") ...
This may be a really dumb question, but... What units does Geography.STLength return? The official MSDN page doesn't say anything about the units returned, and this blog entry here says STLength() returns a float indicating the length of the instance in units. Yes, that's right, it says it returns it in units. Can anyone shed some ligh...
I'd like to rewrite this query for Microsoft Access 2003: SELECT t1.PERSONID ,t1.CARDEVENTDATE ,MIN(t1.CARDEVENTTIME1) AS Intime ,MAX(t2.CARDEVENTTIME1) AS Outtime FROM ( SELECT PERSONID , CARDEVENTDATE , FUNCTIONKEY , CONVERT(VARCHAR(10), SUBSTRING(CARDEVENTTIME...
Hi, I stumbled across this oddity when multiplying DECIMAL numbers on SQL Server 2005/2008. Can anyone explain the effect? DECLARE @a DECIMAL(38,20) DECLARE @b DECIMAL(38,20) DECLARE @c DECIMAL(38,20) SELECT @a=1.0, @b=2345.123456789012345678, @c=23456789012345.999999999999999999 SELECT CASE WHEN @a*@b*@c = @c*@b*@a ...
Hi, I am using SQL Server 2008 R2. Is there a free script/utility which can make CRUD SPs for all of my database tables? I use to have one but lost the script. Thanks ...
Is there a way to search through the set of (Stored Procedures, Functions, Views) for the usage of a constant? I have a problem where I've got a sql server database. It has quite a few stored procedures and functions declared. I'm looking for the usage of "115", which happens to be a pay code. I didn't write all of the code origina...
Say I've got some throw-away sample procedure that looks like this: CREATE procedure simpletestproc_prc ( @companyId int, @objectid float = 5.678, @personname varchar(255) = 'hello world' ) as select @companyId + 10, @objectid, @personname I can use the below query to get the types and names of all...
hello, i'm storing XML values to an entry in my database. Originally, i extract the xml datatype to my business logic then fill the XML data into a DataSet. I want to improve this process by loading the XML right into the T-SQL. Instead of getting the xml as string then converting it on the BL. My issue is this: each xml entry is dynami...
How to restrict the length? Like 12345.789.22.7890 is invalid because the IP ranges from 0-255 Kindly help me ...
What is the best way of extracting the values from vvv.www.xxx.yyy.zzz Note that vvv or www or xxx or yyy or zzz can vary i.e. it can be of any length. For this reason I cannot use substring with charindex. I don't want to do this with LOOP or CURSOR . By using CTE and a number table also it can be done but that will be a bit lengthy ...
I have a column in ntext which holds large unicode strings longer than 4000 chars in length. I need to update/modify the data of the rows of the column in sql but I have no clue how to do so. I have tried nvarchar(max) as a buffer but it truncates the data into 4000 chars. Could anyone help me give me a hint or an idea or a workround so...
I'm using sql Server 2008. following dataPool: PK Col1 Col2 1 SomeValue1 DataToTake1 2 SomeValue1 3 SomeValue1 4 SomeValue1 5 SomeValue2 DataToTake2 6 SomeValue2 ... i want to insert DataToTake1 into Col2 of records with PK 2, 3 and 4 and DataToTake2 into Col2 of record with PK 6. to make it more obvious: record...
Can I return UNIQUEIDENTIFIER from a stored procedure using the RETURN statement or is it only by using the OUTPUT statement? i.e to return the PersonID UNIQUEIDENTIFIER: CREATE PROCEDURE CreatePerson @Name NVARCHAR(255), @Desc TEXT AS DECLARE @Count INT DECLARE @JobFileGUID UNIQUEIDENTIFIER -- Check if job exists? SET @Coun...
Currently have this to get a value from the registry in TSQL. However, I need to get the DigitalProductId and it does not return the value required. I think its stored as a binary in the registry. Any ideas? DECLARE @retvalue int, @data varchar(500) EXECUTE @retvalue = master.dbo.xp_instance_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Micr...
i'll use a concrete, but hypothetical, example. Each Order normally has only one line item: Orders: OrderGUID OrderNumber ========= ============ {FFB2...} STL-7442-1 {3EC6...} MPT-9931-8A LineItems: LineItemGUID Order ID Quantity Description ============ ======== ======== ================================= {098...
Consider tables Table1 id, name 1 xyz 2 abc 3 pqr Table2 id title 1 Mg1 2 Mg2 3 SG1 Table3 Tb1_id tb2_id count 1 1 3 1 2 3 1 3 4 2 2 1 3 2 2 3 3 2 I want to do query to give result like id title 1 MG1 2 MG2 3 Two or More Ti...
I got a join select statement and i only need the last modified field in the second table. here's the select statement that i have now: SELECT NOM,PRENOM,OEDP.NUM_EMP,N_A_S,SIT_STATUT,PERMIS,DATE_EMBAUCHE,ADRESSE1,VILLE1,PROVINCE1,CODE_POSTAL1,TEL_RESIDENCE FROM ODS_EMPLOYE_DOSSIER_PERSONNEL AS OEDP JOIN ODS_SITUATION_POSTE AS OS...
I want to update two tables in one go. How do i do that in SQL Server 2005? UPDATE Table1, Table2 SET Table1.LastName = 'DR. XXXXXX' ,Table2.WAprrs = 'start,stop' FROM Table1 T1, Table2 T2 WHERE T1.id = T2.id and T1.id = '010008' ...
I have a table that list students' grades per class. I want a result set that looks like: BIO...B CHEM...C Where the "B" and "C" are the modes for the class. I can get a mode of all of the grades, but not sure how to get the mode per class ...