i was searching script to extract number from text in sql server and i found this CREATE FUNCTION [dbo].[GetNumbersFromText](@String VARCHAR(2000)) RETURNS @Number TABLE (Number INT) AS BEGIN DECLARE @Count INT DECLARE @IntNumbers VARCHAR(1000) SET @Count = 0 SET @IntNumbers = ” WHILE @Count <= LEN(@String) BEGIN –Find a numeric charactor IF SUBSTRING(@String,@Count,1) >= ‘0’ AND […]
I have to pull a list of integer IDs from a table using only records that match some criteria. For example: Select ProdID From Products Where (ProdType=’XYZ’); The catch is that I have to return it as a set of comma separated values so I can use it to select items in a multi-select list: […]
I need to write a TSQL user defined function which will accept a string and return a number. I will call the function like dbo.EvaluateExpression(’10*4.5*0.5′) should return the number 22.5 Can any one help me to write this function EvaluateExpression. Currently I am using CLR function which I need to avoid. Edit1 I know this […]
I would like to return multiple values in my case statement, such as : SELECT CASE WHEN <condition 1> THEN <value1=a1, value2=b1> WHEN <condition 2> THEN <value1=a2, value2=b2> ELSE <value1=a3, value3=b3> END FROM <table> Of course I can write the case condition multiple times, each time return one value. However, as I have many condition […]
I want to copy a table Equipment from one database MyDBQA to our test database MyDB. There is an identity column in the table which is the primary key (int, not null). But I got an error: Msg 8101, Level 16, State 1, Line 2 An explicit value for the identity column in table ‘MyDB.dbo.Equipment’ […]
The system consists of one central SQL server and two or more satelite servers. The satelite servers collect the measured data and send them to the central server. See the picture: (The picture was taken from the official Service Broker Communication Protocols article and modified.) I need to make the act of adding another satelite […]
Does anyone see what’s wrong with this code for SQL Server? IF NOT EXISTS(SELECT * FROM sys.columns WHERE Name = ‘OPT_LOCK’ AND object_ID = Object_id(‘REP_DSGN_SEC_GRP_LNK’)) BEGIN ALTER TABLE REP_DSGN_SEC_GRP_LNK ADD OPT_LOCK NUMERIC(10, 0) UPDATE REP_DSGN_SEC_GRP_LNK SET OPT_LOCK = 0 ALTER TABLE REP_DSGN_SEC_GRP_LNK ALTER COLUMN OPT_LOCK NUMERIC(10, 0) NOT NULL END; When I run this, I […]
we have a simple table containing category and budget type column as show in the picture above I want to show CATEGORY only once and in place of next two CATEGORY there will be blank like this when I use select * query.
Today, for the first time in 10 years of development with sql server I used a cross join in a production query. I needed to pad a result set to a report and found that a cross join between two tables with a creative where clause was a good solution. I was wondering what use […]
I’m having problems executing a function… Here’s what I did: create a function using sql server management studio. it was successfully created. i then tried executing the newly created function and here’s what I get: The EXECUTE permission was denied on the object ‘xxxxxxx’, database ‘zzzzzzz’, schema ‘dbo’.