SQL Server 2012 :: Dynamic String Replacement

Sep 16, 2015

I have a table with the following data;

CREATE TABLE #Tab (Data Varchar(100))

INSERT INTO #Tab (Data)
Select 'Apple=5,Orange=10,Banana=11' UNION ALL
Select 'Apple=10,Orange=1033,Banana=0' UNION ALL
Select 'Apple = 120,Orange = 1,Banana = 112'
Select * from #Tab

How do I replace every value before the '=' but leave the comma.

Here is what the final output should look like

CREATE TABLE #TabFinal (Data Varchar(100))

INSERT INTO #TabFinal (Data)
Select 'Apple,Orange,Banana' UNION ALL
Select 'Apple,Orange,Banana' UNION ALL
Select 'Apple,Orange,Banana'

Select * from #TabFinal

View 9 Replies


ADVERTISEMENT

String Replacement

Jul 20, 2005

Help, I have a vendor file with the vendors name. I need to add afield that has alternate captionseg. :In my vendor file I have records for AT&T with three differentnames/spellings for AT&T.AT&TLucent TechnologiesA T & TI want to add a new field that would have AT&T for the above records.I know I could hardcode a queryUpdate F_VENDORSet VENDOR_CAPTION_ALT = 'AT&T'where VENDOR_CAPTION like '%Lucent%' orVENDOR_CAPTION like '%A T & T%'however, how can I use another table that has the my search keywordsfor the "like" and the replacement captions so I dont need literalqueries to update all the diffent vendors?I have identified 96 instances of where I want to have an alternativename that I can use for a type of grouping. This gets really wildwith the military such as Army and Navy.TIARob

View 4 Replies View Related

SQL Server 2012 :: Finding Longest String Within A String Field

Mar 20, 2014

We have some URLs within a bulk block of text some of which are very long. I need to identify rows where such urls exceed say 100 characters in length in amongst other text.So the rule would be return a record if within the string there is a string (without spaces) longer than 100 characters.

View 9 Replies View Related

SQL Server 2012 :: Dynamic Value On Pivot

Sep 21, 2015

Can we pass dynamic values while pivoting?

Here is example

Declare @a date, @b date
set @a='2015-09-08 22:19:29.330'
set @b='2015-09-17 22:19:29.330'
create table #DateTemp(Full_Date_Text_YYYY_MM_DD datetime,Full_Date date)
insert into #DateTemp(Full_Date_Text_YYYY_MM_DD,Full_Date)
select '2015-09-09 00:00:00.000','2015-09-09'

[Code] ......

View 3 Replies View Related

SQL Server 2012 :: Dynamic Pivot With Subgrouping

Nov 25, 2013

I am trying to pivot some data as you would normally however I am trying to also group the pivot into three sub column groups too.

Basically the scenario is that I have three sub groups Budget, Person, RenewalDate for each Service (Service being the pivot point). So for each unique service I want to display the budget person and renewal date for each service by company.

I have created two tables to illustrate the base data and the required output.

How to do this dynamically because the number of Services is unknown, i.e. it could be 4 Services or 20, each with three sub columns, budget, person and renewal date.

Please find code below. It should be quite self explanatory as to what I am trying to do.

IMPORTANT:

1. I really need it to be dynamic
2. the Services are not standardised names, they are numbered for illustration purposes only, they vary in naming convention.

create table #BaseData
(
Company nvarchar(100),
Person nvarchar(50),
[Service] nvarchar(100),
Budget int,
RenewalDate datetime
)

[Code] .....

View 4 Replies View Related

SQL 2012 :: Dynamic Partitioning Of Table Server

Dec 3, 2014

I need Dynamic Partition of SQL Table.

1. What is the best practice for partitioning (on date column)

2. The project on which i am working correctly have a case where in i get the update of my status flag after few days (Say 15 - 30) in that case if my data got into partition table how to update and how to search which partition has my data

3. Is creating partition occupies more disk space?

4. Is every partition would need index?

View 7 Replies View Related

SQL Server 2012 :: DATE As Dynamic Table Name

Jan 13, 2015

I am trying to select data from table that have YYMM as table names, they are formatted table1410,table1411, table1412. I am trying to format it like this

declare @tablename60 varchar(50) = 'table' + SUBSTRING(CAST(DATEPART(YY,dateadd(yy, -1, getdate())) as varchar(4)),3,4) + SUBSTRING(CAST(DATEPART(MM,dateadd(mm, -1, getdate())) as varchar(2)),1,2)

But this is hard coding the YYMM, and I would like to have it pull 30,60,90 days fromthe first of the current month. I am having a bit of trouble formatting, how to accomplish this.

View 1 Replies View Related

SQL Server 2012 :: Where Clause In Dynamic Query?

Jul 2, 2015

I am creating a dynamic query where i am appending a where clause something like -

IF (@CurCaptureDate IS NOT NULL)
SET @where_clause = @where_clause + CHAR(10) + 'AND CD.CaptureDate = ' + @CurCaptureDate

PS - CD.CaptureDate is datetime and @CurCaptureDate is also datetime

but when executing , it gives this error - Msg 241, Level 16, State 1, Line 169 Conversion failed when converting date and/or time from character string.

i am not able to use convert here with these quotes.

i tried this - SET @where_clause = @where_clause + CHAR(10) + 'AND CD.CaptureDate = ' + CONVERT(VARCHAR(25),@CurCaptureDate )

but it makes it to -

AND CD.CaptureDate = Jul 19 2014 12:00AM. I would need the date in quotes.

View 4 Replies View Related

SQL Server Replacement

Sep 23, 2004

I've got a SQL Server hardware Replacement to do. This server has a merge replication setup on it which was setup by a contractor.

Do i have to do the configurations manually when moving the replication bits across two servers or is there a different process involved ? I am just not sure how to approach this.

View 1 Replies View Related

SQL Server 2012 :: Join Two Dynamic Pivot Tables

Dec 11, 2013

I have two dynamic pivot tables that I need to join. The problem I'm running into is that at execution, one is ~7500 characters and the other is ~7000 characters.

I can't set them both up as CTEs and query, the statement gets truncated.

I can't use a temp table because those get dropped when the query finishes.

I can't use a real table because the insert statement gets truncated.

Do I have any other good options, or am I in Spacklesville?

View 7 Replies View Related

SQL Server 2012 :: Dynamic Pivot Table Not Grouping

Mar 26, 2014

I have a query

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column
SELECT @ColumnName= ISNULL(@ColumnName + ',','')
+ QUOTENAME(name)

[Code] ....

and so on.

I've tried putting my group by everywhere but it's not working. I'm missing something but what?

View 9 Replies View Related

SQL Server 2012 :: Creating Dynamic Pivot Table

Jul 2, 2014

I am having trouble figuring out why the following code throws an error:

declare
@cols nvarchar(50),
@stmt nvarchar(max)
select @cols = ('[' + W.FKStoreID + ']') from (select distinct FKStoreID from VW_PC_T) as W
select @stmt = '
select *

[Code] ...

The issue that I am having is:

Msg 245, Level 16, State 1, Line 4
Conversion failed when converting the varchar value '[' to data type int.

I know that I have to use the [ ] in order to run the dynamic sql. I am not sure what is failing and why as the syntax seems to be clean to me (obviously it is not).

View 6 Replies View Related

SQL Server 2012 :: Dynamic Return Type In A Function

Mar 3, 2015

I have created a function that will check whether the data is null or not. If its null then it will display that as No data else it will display the original value. Below is the function

GO
Object: UserDefinedFunction [dbo].[fnchkNull] Script Date: 3/4/2015 12:01:58 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

[code]...

The code is working good. However i want the return type to be dynamic. If the data type supplied is integer then i want to return a integer value like 0 if its null. if the data value is varchar then i want to return 'No Data'.

View 7 Replies View Related

SQL Server 2012 :: Dynamic Query On DB Table Names

Mar 9, 2015

I have query which is used to dynamically insert value but not working. We are trying to get all table names and insert dynamically to all tables by looping through table names.

declare @a varchar(max),@i int;
declare @table table(rno int, name varchar(max))
declare @b varchar(max)
insert into @table
select row_number() over( order by table_name) rno, table_name from INFORMATION_SCHEMA.tables
declare @tblname varchar(max)

[Code] .....

View 1 Replies View Related

SQL Server 2012 :: Table Variable In Dynamic Query?

Jul 2, 2015

I have started working with dynamic queries recently. I am using a table variable and need to add a join in query dynamically.

For Eg- @TableVariable

SET @query_from = @query_from + CHAR(10) + ' JOIN @TableVariable on ABC.ID = @TableVariable.ID '

BUt it gives an error that @TableVariable must be declared

View 8 Replies View Related

SQL Server 2012 :: Build Search Condition Dynamic

Oct 6, 2015

IF OBJECT_ID('tempdb..#test') IS NOT NULL
DROP TABLE #test
CREATE TABLE #test (TestID CHAR(5) NOT NULL PRIMARY KEY)
INSERT INTO #test
SELECT '1'

[code]....

i am trying to build a dynamic where "or" clause finding difficulties.

View 7 Replies View Related

SQL Server 2012 :: Dynamic Table Pivot With Multiple Columns

Jan 23, 2014

I am trying to pivot table DYNAMICALLY but couldn't get the desired result .

Here is the code to create a table

create table Report
(
deck char(3),
Jib_in float,
rev int,
rev_insight int,
jib_out float,

[Code] .....

Code written so far. this pivots the column deck and jib_in into rows but thats it only TWO ROWS i.e the one i put inside aggregate function under PIVOT function and one i put inside QUOTENAME()

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns += N', p.' + QUOTENAME(deck)
FROM (SELECT p.deck FROM dbo.report AS p
GROUP BY p.deck) AS x;

[Code] ....

I need all the columns to be pivoted and show on the pivoted table. I am very new at dynamic pivot. I tried so many ways to add other columns but no avail!!

View 1 Replies View Related

SQL Server 2012 :: Convert Hardcoded SP Into Dynamic Merge Statement

Feb 13, 2015

I am working on to convert my static Store procedure to Dynamic.

I have created a Store procedure with Merge statement which is inserting new record and updating existing record.

This SP I will use in SSIS Insted of Data Flow Task I will run in Execute SQL Task.

Now my biggest problem is I dont know how to convert static code toi dynamic

Below is my Store procedure code.

As you can see my Source Query

I have a filemaster table as shown below which consist of Input filename,Source table ,Destination table and BBX expression.

Input_FilenameSourceTableName DestinationTableName BBxKeyDerExpr
CCTFB ImportBBxCctfb ArchiveBBxCctfb SUBSTRING(Col001,1,6)
CEMXR ImportBBxCemxr ArchiveBBxCemxr SUBSTRING(Col001,1,10)

In my source query I want to change the value of Source table ,Destination table and BBX expression dynamically on the basis of input file.

Purpose of making dynamic is that I have created separate sp for all the input, my clients want to have sungle dynamic sp which will execute on the basis of input file.this input file name I wil get fromm variable which i have created in SSIS Package.

Lets consider @File_name is the variable in package which store the file name

if file name is CCTFB then my query should take the Source table ,Destination table and BBX expression value from file master table.

Like that I have 100 of source query and evry query have diffrent number of columns. How can I change the column number in uodate and insert statement dynamically on run time.

CAST(SUBSTRING(Col001,1,6) + SUBSTRING(Col002,1,10) AS varchar(100)) :-It creates a key for comparing, this value i can take it from filemaster
HASHBYTES('MD5', CAST(CHECKSUM(Col001, Col002,Col003,Col004) AS varchar(max))) -here numberv of column need to be changed .
(SUBSTRING(SOURCE.Col001,1,6) + SUBSTRING(SOURCE.Col002,1,10)) this condition also i can take it from file master.

[Code] ....

I am able to get inserted and updated rowcount, but not able to get the matching records count.

View 0 Replies View Related

SQL Server 2012 :: How To Join Pivot Results With Dynamic Columns

Mar 5, 2015

I have a lookup table, as below. Each triggercode can have several service codes.

TriggerCodeServiceCode
BBRONZH BBRZFET
BBRONZH RDYNIP1
BBRONZP BBRZFET
BCSTICP ULDBND2
BCSTMCP RBNDLOC

I then have a table of accounts, and each account can have one to many service codes. This table also has the rate for each code.

AccountServiceCodeRate
11518801DSRDISC -2
11571901BBRZFET 5
11571901RBNDLOC 0
11571901CDHCTC 0
17412902CDHCTC1 0
14706401ULDBND2 2
14706401RBNDLOC 3

What I would like to end up with is a pivot table of each account, the trigger code and service codes attached to that account, and the rate for each.

I have been able to dynamically get the pivot, but I'm not joining correctly, as its returning every dynamic column, not just the columns of a trigger code. The code below will return the account and trigger code, but also every service code, regardless of which trigger code they belong to, and just show null values.

What I would like to get is just the service codes and the appropriate trigger code for each account.

SELECT @cols = STUFF((SELECT DISTINCT ',' + ServiceCode
FROM TriggerTable
FOR XML PATH(''), TYPE
).value('(./text())[1]', 'VARCHAR(MAX)')
,1,2,'')

[Code] ....

View 1 Replies View Related

SQL Server 2012 :: Dynamic Query To Print Out Resultset From A Table?

Sep 9, 2015

I wan to print out the dynamic query result so that i can use as a script for some tasks.This is the scenario wher i got stuck, i am not able to print out the result as it return only the last value because of OUTPUT param limitation

Is there any way to print all the 3 INSERT stmt.

IF OBJECT_ID ('tempdb.dbo.#temp') IS NOT NULL
DROP TABLE #temp
CREATE TABLE #temp (Command varchar(8000))
INSERT INTO #temp
SELECT 'INSERT INTO Test1(column1,column2)values(1,2)'
UNION ALL
SELECT 'INSERT INTO Test2(column1,column2)values(1,2)'

[code]....

View 4 Replies View Related

Replacement For ISAPI In SQL Server 2005

Oct 10, 2006

Hi everyone, I have some code that we need to migrate to SQL Server2005 from 2000, and I have a webpage that upon viewing, fires a queryto the SQL server using ISS and ISAPI. The result set is formatted fordisplay using XSLT. But since ISAPI is deprecated in SQL 2005, I waswondering how to migrate this.Thanks,

View 5 Replies View Related

SQL Server 2012 :: Read Dynamic Columns From Excel File Into SSIS

Nov 11, 2014

I have an excel file which has dynamic columns

i.e. Col1, Col2, Col3 this week. next week i will have a new Col4 in the sheet. This will keep on adding every week.

My problem is to Unpivot the data

Date 8/2/2013 8/9/2013 8/16/2013

Stock 1,561 1,661 1,761

i.e. the abobe table should become as

Date Stock

8/2/2013 1561
8/9/2013 1661
8/16/2013 1,761

How can I unpivot the dynamic columns given that the columns will keep on increasing every week.

View 1 Replies View Related

SQL Server 2012 :: Convert Rows To Columns Using Dynamic PIVOT Table

Feb 3, 2015

I have this query:

SELECT TOP (100) PERCENT dbo.Filteredfs_franchise.fs_franchiseid AS FranchiseId, dbo.Filteredfs_franchise.fs_brandidname AS Brand,
dbo.Filteredfs_franchise.fs_franchisetypename AS [Franchise Type], dbo.Filteredfs_franchise.fs_franchisenumber AS [Franchise Number],
dbo.Filteredfs_franchise.fs_transactiontypename AS [Transaction Type], dbo.Filteredfs_franchise.fs_franchisestatusname AS [Status Code],

[Code] ....

I need to pivot this so I can get one row per franchiseID and multiple columns for [Franchisee Name Entity] and [Franchise Name Individual]. Each [Franchisee Name Entity] and [Franchise Name Individual] has associated percentage of ownership.

This has to be dynamic, because each FranchiseID can have anywhere from 1 to 12 respective owners and those can be any combination of of Entity and Individual. Please, see the attached example for Franchise Number 129 (that one would have 6 additional columns because there are 3 Individual owners with 1 respective Percentage of ownership).

The question is how do I PIVOT and preserve the percentage of ownership?

View 3 Replies View Related

SQL Server 2012 :: Dynamic Pivot Statement To Calculate And Organize Columns

Mar 11, 2015

How to write a Dynamic Pivot Statement to Calculate and Organize Columns like:

CREATE TABLE #mytable
(
Name varchar(50),
GA int,
GB int,
startdate DATETIME,
enddate DATETIME

[Code] ...

Below is Our Sample Table Data.

Name GAGBstartdateenddate
Pavan 261/1/20151/1/2015
Hema 561/1/20151/1/2015
Surya 501/1/20151/1/2015
Pavan 811/2/20151/8/2015
Hema 311/2/20151/8/2015
Surya 121/2/20151/8/2015
Pavan 1041/9/20151/15/2015
Hema 301/9/20151/15/2015
Surya 6131/9/20151/15/2015

How to write Pivot Satement to get Oupt like below:

1/1/2015 Pavan Hema Surya SumTotal
Total 8 11 5 24
GA 2 5 5 12
GB 6 6 0 12

1/8/2015 Pavan Hema Surya SumTotal
Total 9 4 3 16
GA 8 3 1 12
GB 1 1 2 4

1/15/2015 Pavan Hema Surya SumTotal
Total 14 3 19 36
GA 10 3 6 19
GB 4 0 13 17

View 5 Replies View Related

SQL Server 2012 :: Creating A View Or Procedure From Dynamic Pivot Table

May 29, 2015

I have written a script to pivot a table into multiple columns.

The script works when run on its own but gives an error when i try to create a view or aprocedure from the same script. The temporary table #.... does not work so i have converted it to a cte.

Here is a copy of the script below

-- Dynamic PIVOT
IF OBJECT_ID('#External_Referrals') IS NULL
DROP TABLE #External_Referrals;
GO
DECLARE @T AS TABLE(y INT NOT NULL PRIMARY KEY);

[Code] ....

View 7 Replies View Related

SQL Server 2012 :: In Trigger - Building Dynamic Table With Inserted Data

Nov 4, 2015

Within a trigger, I'm trying to create a unique table name (using the NEWID()) which I can store the data that is found in the inserted and deleted tables.

Declare @NewID varchar(50) = Replace(convert(Varchar(50),NEWID()),'-','')
Declare @SQLStr varchar(8000)

Set @SQLStr= 'Select * into [TMPIns' + @newID + '] from inserted'
Exec (@SQLStr)

I get the following error: Invalid object name 'inserted'

I know I can do:

Select * into #inserted from inserted
Set @SQLStr= 'Select * into [TMPIns' + @newID + '] from #inserted'
Exec (@SQLStr)

But I don't want to use TempDB as these tables can become big and I also feel that it is redundant. Is there a way to avoid the creation of #inserted?

View 2 Replies View Related

Urgent - Replacement Of Number Function In Sql Server

Apr 10, 2001

Hey folks,

In Sybase SQL Any where, we have a function called Number (*) which will in turn generate serial numbers like ex..

Select Number (*) from x

The output will be

Number(*)
1
2
3
4
5
6
and so on..


I need a equivalent function in SQL Server 7.0 in which if i do select on that particular function with a table i should return above values..

Can any one solve this issue...

Please help me in this....

Urs
VJ

View 1 Replies View Related

Token Replacement In SQL Server - Any Down Side To Turning It On?

Jan 7, 2008

Subject says it all, really. I want to start using Token Replacement,but do I break anything by enabling it? Do jobs that don't use tokensrequire any changes? I saw somewhere that it can't be turned off, soI'm paranoid about enabling it. Anything that I should be aware of?Many thanks.

View 2 Replies View Related

Moving Replicated Databases To A Replacement SQL Server

May 23, 2006

Any advice that can be offered on this subject would be much appreciated.

We are debating whether to backup/restore the replicated databases or simply copy the mdf and ldf files accross and Attach them. The question is whether the replication information is retained for the publshed objects when the files are copied across? Alernatively, must we use backup/restore with KEEP_REPLICATION set for the restore?

We also plan to back up and restore the distribution, publication, master, msdb etc databases as well as rename the SQL Server to have the same name as the original.

Do these things need to happen in any particular order?

Does the timing of the renaming of the SQL Server matter?

At least some of the these operations (restoring the master database and renaming the SQL Server) need to be done in single user mode. Is is best to restore all the system databases in single user mode?

Lastly, is it necsssary (from a SQL Server point of viewpoint) to rename the computer to have the same name as the SQL Server?



Thanks,



Dick Campbell

View 1 Replies View Related

Is SQL Server Express A Replacement To MS Access Databases?

Feb 27, 2008

I have a small client installable web application which used to work great on 32 bit machines. Well now clients are starting to use x64 Windows 2003 servers and things have begun to unravel. W2K3 x64 does not have any of the JET database drivers needed to communicate to the Access databases and it doesn't look like they're coming anytime soon.

Is SQL Server Express the new replacement for Access databases on x64 machines. Does Microsoft intend on moving some of the JET drivers over from 32 bit servers. I know that you can run IIS in 32 bit mode and everything is fine, but I can't ask everyone to do that.

Is SQL Server Express something that I can expect people to have installed, it sure doesn't sounds like it. After reading some posts, it sounds like it's a bit of a hastle to install.

Thanks in advance for your time.

-Mark

View 3 Replies View Related

SQL Server 2012 :: SELECT A String

Dec 1, 2014

I have string as below:

InvoiceTemplateId= SOURCE.InvoiceTemplateId
,Name= SOURCE.Name
,DetailTotalUnitsQty= SOURCE.DetailTotalUnitsQty
,InsertedDate= SOURCE.InsertedDate
,UpdatedDate= SOURCE.UpdatedDate
,Distributor_Id= SOURCE.Distributor_Id
,InsertedBy= SOURCE.InsertedBy
,UpdatedBy= SOURCE.UpdatedBy

I need a string Like below:

Name= SOURCE.Name
,DetailTotalUnitsQty= SOURCE.DetailTotalUnitsQty
,InsertedDate= SOURCE.InsertedDate
,UpdatedDate= SOURCE.UpdatedDate
,Distributor_Id= SOURCE.Distributor_Id
,InsertedBy= SOURCE.InsertedBy
,UpdatedBy= SOURCE.UpdatedBy

So I need every thing except the First value before first comma .

View 3 Replies View Related

SQL Server 2012 :: Day Wise And Date Range Calculation With Looping Or Dynamic Data?

May 14, 2015

I am using Sql Server 2012.

This is how I calculate the ratio of failures in an order:

31 Days Table 1 query
sum(CASE
WHEN (datediff(dd,serDATE,'2015-01-21')) >= 31 THEN 31
WHEN (datediff(dd,serDATE,'2015-01-21')) < 0 THEN 0
ELSE (datediff(dd,serDATE,'2015-01-21'))END) as 31days1 .

How do i loop and pass dates dynamically in the Datediff?

31 Failures Table 2 query
SUM(Case when sometable.FAILUREDATE BETWEEN dateadd(DAY,-31,CONVERT(DATETIME, '2015-01-21 23:59:00.0', 102))
AND CONVERT(DATETIME, '2015-01-21 23:59:00.0', 102)Then 1 Else 0 END) As Failures31,31 Day Cal(Formula) combining both Table 1 and Table 2
((365*(Convert(decimal (8,1),T2.Failures31)/T1.31day))) [31dayCal]This works fine when done for a specific order.

I want a similar kind of calculation done for day wise and month wise.

2. what approach should I be using to achieve day wise and month wise calculation?

I do also have a table called Calender with the list of dates that i can use.

View 3 Replies View Related

SQL Server 2012 :: More Than 1 Alphanumeric Chars In A String

Mar 12, 2014

I have an employee table

Surname GivenName
ABC x.yz
A.BC X.*YZ
A*.BC xyz

The query needs to get me the surname and givenname which have more than 1 non alphanumeric characters. In this case the output will be

a.bc x.*yz
a*.BC xyz

I know how to find non alphanumeric chars in a string but not sure if I can how to find the strings with minimum 2 non alphanumeric chars?

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved