SQL Server 2014 :: Creating A Delimited String?

Apr 7, 2015

I have a need to create a delimited string so that I can use this to create a data driven subscription on SSRS.

In the below code, I need to create a delimited string using the branch number, grouped by the email address

USE tempdb
GO
IF OBJECT_ID('tempdb..#emails') IS NOT NULL
BEGIN
DROP TABLE #emails

[code]....

View 7 Replies


ADVERTISEMENT

SQL Server 2014 :: Sanitizing Inputs When Creating String By Concatenation

Mar 19, 2014

I have a need to create a table in a sql server database from C# code. The kicker is that the user must be able to specify the table and field names via the UI. I can do a bit of sanity checking but as long as they enter something reasonable I need to accept it. Normaly I always ADO parameters to sanitise any user parameters but they can't be applied to table and field names, only values. As far as I'm aware that leaves me needing to concatenate strings and that's something I usually avoid like the plague due to risk of SQL injection.

My actual question : Assuming string concatenation is my only way forward, how can I sanitise the values that would go into the table name and fieldname bits of a CREATE TABLE statement to ensure that injection can't occur? I've been pondering it and I think I just need to check for semi-colons. Without a semi-colon I don't think a user could inject an extra statement could they?

View 3 Replies View Related

SQL Server 2008 :: Search Each And Every String In Comma Delimited String Input (AND Condition)

Mar 10, 2015

I have a scenario where in I need to use a comma delimited string as input. And search the tables with each and every string in the comma delimited string.

Example:
DECLARE @StrInput NVARCHAR(2000) = '.NET,Java, Python'

SELECT * FROM TABLE WHERE titleName = '.NET' AND titleName='java' AND titleName = 'Python'

As shown in the example above I need to take the comma delimited string as input and search each individual string like in the select statement.

View 3 Replies View Related

SQL Server 2012 :: Hyphen Delimited String Manipulation

Oct 19, 2015

I am having a field 'Flight Route that holds hyphen delimited character sequences.

E.g. : ABC-BCD-DEF-EFG.

My requirement is like this:

If the flight route is:

ABC-BCD-BCD-DEF make it ABC-BCD-DEF
ABC-ABC-BCD-DEF make it ABC-BCD-DEF

i.e. 'whenever a sequence repeats,only one appearance of that sequence should be displayed.The field Flight Route has to be updated with this replaced string.

View 6 Replies View Related

SQL Server 2014 :: Find String With Spaces And Replace Them With Other String

Sep 8, 2015

I have following query which return me SP/Views and Functions script using:

select DEFINITION FROM .SYS.SQL_MODULESNow, the result looks like
Create proc
create procedure
create proc
create view
create function

I need its result as:

Alter Procedure
Alter Procedure
Alter Procedure
Alter View
Alter Function

I used following

select replace(replace(replace(DEFINITION,'CREATE PROCEDURE','Alter Procedure'), 'create proc','Alter Procedure'),'create view','Alter View') FROM .SYS.SQL_MODULESto but it is checking fixed space like create<space>proc, how can i check if there are two or more spaces in between create view or create proc or create function, it should replace as i want?

View 5 Replies View Related

Creating A Tab Delimited Export

Sep 5, 2006

Hello all,

I am trying customize the CSV rendering extension with different device information settings. I would loke to change the CSV extension to a Tab delimited output. However, whenever I add a new extension line to the RSReportServer.config file I run into problems.

1. What is the correct character to use in the delimiter to specify a Tab character? I seem to be unable to get this correct

2. Why when I override the name in the rendering extension does it still show up as the CSV (comma delimited) name in the export dropdown.

Here is my customized rendering extension.

<Extension Name="TXT" Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
<OverrideNames>
<Name>TXT (Tab Delimited Text File)></Name>
</OverrideNames>
<Configuration>
<DeviceInfo>
<Encoding>ASCII</Encoding>
<FieldDelimiter><![CDATA[#x9]]></FieldDelimiter>
<NoHeader>true</NoHeader>
</DeviceInfo>
</Configuration>
</Extension>

Thanks

Justin

View 8 Replies View Related

Creating A Comma Delimited List

Jul 11, 2005

Hi,

I have a complex query where each row in the final dataset is a
product.
However each product has a number of authors associated with it. 
What I
would like to do is have a query/subroutine join the authors to the
product,
as a string:


ProductID  
Title                       
Authors
1           The Sacred and the Profane   John Rieggle, George
Alexi
2           Dancing
in the Dark          Dan
Brown, Peter Kay, Paul
Dwebinski

Products
Table
==============
ProductID
Title

Authors
Table
=============
AuthorID
Name

Product Authors
Table
=====================
AuthorID
ProductID

Is this at all
possible?

Thanks

jr.

View 3 Replies View Related

Creating Columns From A Comma Delimited Row

Aug 20, 2007

Hi All

This is my first official post...very exciting:)

OK....

I need to split a string, based on comma delimetrs, into columns of a table.

I am using SQL Server 2005.
The plan is to use webmethods (Integration Software) to receive a flat file, loop through the records in that flat file and during each iteration of the loop call a SQL stored procedure that will split the record, based on comma delimetrs, into columns of a table.

A typical record will look like this:
AP05-07,ACTUAL,ZA01,......

I have looked at some of the past solutions to this type of post, but am battling to understand....

So if its possible, is there a simple way to create a stored procedure that will do this?

Many thanks

View 4 Replies View Related

Creating A Delimited List Of Data In SP W/O Cursor

Mar 29, 2001

SQL7 SP3

Hi.

I have a table in which I want to create a delimited list of values from one field which I will be using for validation.

How can I do this without using a cursor to build the string. The SQL would be something like:

SELECT *
FROM myCrossRefTable
WHERE SourceTable = 'FieldValueList'

I'm looking to return on string like -

~value1~value2~value3~value4~value5

Thanks,

Craig

View 2 Replies View Related

Creating A New SQL Table By Importing In A Delimited File

Feb 17, 2008



How do I do this?

I cannot find any facility like there is a Access for getting external data.

View 3 Replies View Related

SQL Server Admin 2014 :: What Is The Use Of Creating Manifest File In SSIS

Apr 8, 2014

What is the use of creating manifest file in SSIS... because I was schedule a job and its not needed and manifest file then why we are creating this.

View 4 Replies View Related

SQL Server Admin 2014 :: Creating Named Instance On Cluster

Apr 22, 2015

I need to create a named instance on a sql cluster.

How do I perform this? Is it as simple as creating a named instance on a standalone machine? Is there anything I need to consider?

View 3 Replies View Related

Parsing Delimited String

Jul 3, 2013

parsing any delimited string (in above example it is using ',' as parsing delimiter. This query can be useful in many business scenarios where in we have input data as a long string containing delimited values.

declare
v_sql VARCHAR2(2000);
v_pos INTEGER;
v_differentiator VARCHAR2(10);

[code]...

View 4 Replies View Related

SQL Server 2014 :: Comparing String With Date

Jan 28, 2014

I have to write a where condition where i need to compare string with date

I have 2 columns FROMDATE and TODATE with datatype varchar(9)

The strings in the columns looks like YYYYMMDD+'1' or YYYYMMDD+'2' here 1 is Am and 2 is PM

i.e., 201401011 or 201401012

So I need to chop off last character before using them in WHERE condition.

Now I need to write a where condition like [if todays date is in between fromdate and todate columns then return rows.

if FROMDATE column is null it should take minimum date 1900/01/01 if TODATE is null then date should be 9999/01/01

The query i wrote is

select * from Table where ISNULL(SUBSTRING(VALIDTO,1,8),'19000101') > = getdate()

AND ISNULL(SUBSTRING(VALIDTO,1,8),'99991231') < convert(varchar, getdate(), 112)

but it shows no data...

View 9 Replies View Related

Quotes Around The Comma Delimited String

Oct 18, 2005

I am trying to build a dynamic where  statement for my sql stored prcoedure
  if len(@Office) > 0   select @strWhereClause = N'cvEh.chOfficeId in (select id from dbo.csfParseDeLimitedText( ' + @vchOffice  +  ',' + ''',''' + '))' + ' and ' + @strWhereClause
In this case users can enter comma delimited string in their search criteria. So if they enter we1, we2, we3 then my sql statement should look like
select @strWhereClause = cvEh.chOfficeId in (select id from dbo.csfParseDeLimitedText('we1', 'we2', 'we3'),',')
My csfParseDeLimitedText function looks like this
Create  FUNCTION [dbo].[csfParseDeLimitedText] (@p_text varchar(4000), @p_Delimeter char(1))RETURNS @results TABLE (id varchar(100))ASBEGIN declare @i1 varchar(200)declare @i2 varchar(200)declare @tempResults Table (id varchar(100))while len(@p_text) > 0 and charindex(@p_Delimeter, @p_text) <> 0beginselect @i1 = left(@p_text, charindex(@p_Delimeter, @p_text) - 1)insert @tempResults select @i1select @p_text = right(@p_text, len(@p_text) - charindex(@p_Delimeter,@p_text))endinsert @tempResults select @p_text
insert @resultsselect *from @tempResultsreturnEND
 
My problem is it does not put quotes around the comma delimited stringso I want to put 'we1' , 'we2'. These single quotes are not coming in the dynamic sql statement. How can I modify my query so that single quotes around each entry should show up.
Any help will be greatky appreciated.
Thanks

View 1 Replies View Related

SELECT WHERE IN Comma Delimited String

Jun 3, 2008

Hello

I have a table with column Options where each field contains a comma delimited list of ID numbers.

I want to select any records where a certain ID number appears in that list.

So something like:

SELECT * FROM Table t1
WHERE MyID IN (SELECT Options FROM Table t2 WHERE t1.ID = t2.ID)

But that gives me the error:

Syntax error converting the varchar value '39,20' to a column of data type int.

I feel I'm close though! Could anyone point me in the right direction?

Many thanks

Square

View 3 Replies View Related

Insert A Comma Delimited String

Nov 22, 2013

I have a problem in a stored procedure trying to insert a comma delimited list into rows.The parameters for example would be somthing like....

@GarageID int = 20,
@Cars nVarChar(200) = Ford~Fiesta,BMW~320,Volvo~340,Jaguar~XJS

I need to split where the comma is for each new row, and separate each value from either side of the '~' like so....

GarageID Make Model
--------------------------------
20 Ford Fiesta
20 BMW 320
20 Volvo 340
20 Jaguar XJS

View 2 Replies View Related

Splitting A Comma Delimited String

Oct 1, 2007

I have a string like say: '3:4:5:4,2:4:1,4:1:2:5:2'. Now I need to split the substrings delimited by commas. So my final output shall be

3:4:5:4
2:4:1
4:1:2:5:2


I did write a piece of code to achieve the same, but I feel its not so efficient. Can anyone suggest me a better way, if any? My code is as follows:

Declare @person as varchar(255), @cnt smallint,@loc smallint,@prevloc smallint, @str varchar(255)
Select @prevloc=0,@loc=1,@cnt=1,@person = '3:4:5:4,2:4:1,4:1:2:5:2'
While @loc != 0
begin
set @prevloc=(case when @loc = 1 then 0 else @loc end) +1
set @loc = charindex(',',@person,@loc+1)
Set @str = substring(@person,@prevloc,(Case when @loc = 0 then len(@person) - @prevloc + 1 else @loc - @prevloc end))
print 'String = ' + @Str
set @cnt=@cnt+1
end

RESULT
------
String = 3:4:5:4
String = 2:4:1
String = 4:1:2:5:2


Note: My actual purpose is to also sub split it again with ':' delimiter too. So looking for an efficient code.

View 4 Replies View Related

SQL Server 2014 :: Function To Convert Datetime To String

Jan 26, 2014

Writing a SQL Function as below

the input parameter for function should be datetime of sql datetimeformat

and out put should be a string = yyyymmdd1 or yyyymmdd2

The last character 1 or 2 based on below condition

if time is between 6AM and 5.59PM then 1
if 6PM to 5.59AM then 2

View 6 Replies View Related

Stored Procedure And Comma Delimited String

Dec 8, 2006

I'm passing a comma delimited string to my SP, e.g.:"3,8,10,16,23,24"I need to retreive each number in this string and for every number found I need to execute some sode, say add "AND SportID="+numberfoundHow can I do that?

View 6 Replies View Related

Multiple Rows Into A Comma Delimited String

May 22, 2007

I have the following table:id name1 yes2 no3 what4 is5 this6 niceThe amount of rows can vary from 1 to 50. I only need the name column.What SQL statement do I have to execute to get the following:yes,no,what,is,this,nice,  (trailing , is acceptable)Thanks!

View 1 Replies View Related

Splitting Space Delimited String Inline

May 20, 2008

So we have a field called forenames, and it needs to be split into fields forename_1, forename_2, forename_3, forename_4 (don't ask).

Ok, I've come up with this so far, which works, but is pretty nacky in my opinion. Has any one got a better way of achieving this?

SELECT forenames
, Replace(forenames, ' ', '.')
, Reverse(ParseName(Replace(Reverse(forenames), ' ', '.'), 1)) As [f1]
, Reverse(ParseName(Replace(Reverse(forenames), ' ', '.'), 2)) As [f2]
, Reverse(ParseName(Replace(Reverse(forenames), ' ', '.'), 3)) As [f3]
, Reverse(ParseName(Replace(Reverse(forenames), ' ', '.'), 4)) As [f4]
FROM ( SELECT 'John' As [forenames]
UNION SELECT 'John Paul'
UNION SELECT 'John Paul George'
UNION SELECT 'John Paul George Ringo'
) As [x]

Results

forenames (no column name) f1 f2 f3 f4
---------------------- ---------------------- ---- ---- ------ -----
John John John NULLNULL NULL
John Paul John.Paul John PaulNULL NULL
John Paul George John.Paul.George John PaulGeorgeNULL
John Paul George Ringo John.Paul.George.Ringo John PaulGeorgeRingo

View 7 Replies View Related

How To Parse A String Column With Comma Delimited

Jul 20, 2005

Hi,I would like to parse out each value that is seperatedby a comma in a field and use that value to join to another table.What would be the easiest way to do so without having towrite a function or routine ?EX.Table AAACOL1 COL21 11, 124, 1562 11, 505, 600, 700, ...Table BBBCOL1 COL211 Desc11124 Desc124156 Desc 156

View 2 Replies View Related

Transact SQL :: How To Split Comma Delimited String

Oct 8, 2008

I have a parameter called Id in my SP which will be of nvarchar data type and i'm going to get the multiple ids at a time seperated by commas in that parameter from the application. Now my requirement is to update a table with these comma seperated ids in seperate rows.

For example, if i have 2 parameters called Id1 and Id2. Id1 will contain only one value and will be of int data type and Id2 will be of nvarchar data type as i can get multiple ids delimited by a comma from the application.

Suppose Id1 = 1 and Id2 = '1,2,3,4'. Then I have to update id2 in the tables seperately like wherever Id1 is '1' i need to update Id2 column for 4 rows with the value 1, 2, 3, 4 respectively in different rows.
 
how can i do this in T-SQL? How can i split the data of parameter Id2 in 4 different rows?

View 24 Replies View Related

SQL Server Admin 2014 :: Window Authentication Using Connection String

May 12, 2015

Is it possible to make window authentication using Connection String.

Like
Data Source="Remote Host"; User ID="user1"; Password="Password"
Integrated Security=SSPI'

user1 have rights on SQL server.

Is it possible user2 logged in on different machine can access SQL server.

user2 does not have rights on SQL Server.

View 3 Replies View Related

SQL Server 2014 :: Query To Split String As Rows And Columns

Oct 19, 2015

I have a string that contains series of parameters with separators.i need to split the parameters and its values as rows and columns.e.g string = "Param1 =3;param2=4,param4=testval;param6=11;..etc" here the paramerter can be anything and in any number not fixed parameters.
Currently am using the below function and getting the parameters by each in select statement as mentioned below.

select [dbo].[rvlf_fn_GetParamValueWithIndex]('Param1=3;param2=4,param4=testval;param6=11;','param1=',';') as param1,
[dbo].[rvlf_fn_GetParamValueWithIndex]('Param1=3;param2=4,param4=testval;param6=11;','param2=',';') as param2
CREATE FUNCTION [dbo].[rvlf_fn_GetParamValueWithIndex]
(
@CustomProp varchar(max),

[code]....

View 8 Replies View Related

SQL Server 2014 :: Select All Top Level File Paths From A String Value?

Oct 26, 2015

I have a problem where I need to select all top level file paths from a string value in SQL

So I have a column "Locations"

Example Data:

X:folderanotherfolder
X:folderyet another folder
X:foldernameanother folder
X:foldernameyet another folder

I'd want to return only:

X:folder
X:foldername

I need to somehow parse the sting and capture anything before the second ''?

View 4 Replies View Related

Sending A Delimited String To A As Input Stored Procedure

Jun 28, 2004

if i send the string

2,3,4,5 to a stored procedure...

is there a way i could split those values for input into the database? no, right? i would need a seperate stored procedure that would take each value one at a time...correct?

View 7 Replies View Related

How Do I Split And Insert A Comma Delimited String To A Table?

Oct 13, 2004

I am passing in a string of dates, delimited by a comma.

So

01/01/04, 02/01/04, 03/01/04

etc

I would like to enter each of these values into a table via an INSERT stored procedure.

Does anyone have any code for this?

View 1 Replies View Related

T-SQL (SS2K8) :: Split Pipe Delimited String Into Two Columns

Mar 6, 2014

I have a single string "XYZ00001|Test_b|XYZ00002|Test_a|XYZ00003|Test_c" that will continue to grow over time.

Is there a way I can extract the values from the string into two separate columns?

XYZ0001 Test_b
XYZ0002 Test_a
XYZ0003 Test_c

View 5 Replies View Related

Stored Procedure To Parse Delimited String To Table?

Apr 10, 2015

SP to parse a delimited string and insert the result in a table. I am using SQL Server 2008 R2. I have 2 tables - RawData & WIP. I have Robots on a manufacturing line capable of moving data to a DB. I move the raw data to RawData. On insert [RawData], I want to parse the string and move the contents to WIP as indicated below. I will run reports against the WIP Table.

Also, after the string is parsed, I'd like to change the Archive column, the _0 at the end of the raw string to 1 in the WIP table to indicate a successful parse.

Sample Strings - [RawData Table]
04102015_114830_10_013_9_8_6_99999_Test 1_1_0
04102015_115030_10_013_9_8_6_99999_Test 2_1_0

Desired Output - [WIP Table]

Date Time Plant Program Line Zone Station BadgeID Message Alarm Archive
-----------------------------------------------------------------------------------
04102015 114830 10 13 9 8 6 99999 Test 1 1 1
04102015 115030 10 13 9 8 6 99999 Test 2 1 1

View 16 Replies View Related

SQL Server 2014 :: Script To Search For A String In All DB Objects (Including Synonyms)

Jun 29, 2015

I'm looking for a "God-script" to search for a given string (case invariant) in all DB objects (esp synonyms) in all DBs on a server?

View 9 Replies View Related

Conditional Where Clause With Comma Delimited String And Link Table

Jul 24, 2007

 I have 3 tables:tblUsersuserID int PK(...)tblSportsSportID int PK(...)tblUsersAndSports  (contains the link between users and sports..a single user may have multiple entries in this table)Usercode intSportID intNow I want a stored proc that enables visitors to search on all user that have a specific sportID.The SportIDs to search on are in the var @sports as a comma delimited string,like '3,6,7'@sports may also be null (or an empty string if that is more convenient for building the SQL) when a visitor does not want to search on any of the sports a user practices, in that case no selection based on the sport criteria should be done, so ONLY filter on sports when the value of @sports is not nullpseudo code:select * from tblUserswhere   if @sports not null    user.sports in @sportsand username=@usernameand age=@agehelp is greatly appreciated!

View 10 Replies View Related







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