Extract numbers from a text in SQL Server

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 […]

How can I pull a list of ID's from a SQL table as a comma-separated values string?

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: […]

String Expression to be evaluated to number

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 […]

case statement in SQL, how to return multiple variables?

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 […]

Set IDENTITY_INSERT ON is not working

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’ […]

SQL Service Broker — one central SQL and more satelite SQL… beginner wants to understand details

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 […]

Invalid column name on sql server update after column create

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 […]

how to show column value only one time if it is repeated and blank until different value comes in sql

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.

sql cross join – what use has anyone found for it?

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 […]

The EXECUTE permission was denied on the object 'xxxxxxx', database 'zzzzzzz', schema 'dbo'

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’.

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.