SQL Server 2012 :: String Concatenation Using + Operator With Semicolon Delimiter
Dec 5, 2013
I have 8 fields - I have requirement to concatenate using '+' operator with semicolon delimiter but issues is in the
Output I get semicolons for the fields that are empty below is my code :
-------------
case
when [SLII Request Type] ='Job Posting' and [SmartLaborII Request Status] like 'Pending Approval (Level 4%'
and [New Extension or Replacement Audit Flag] like 'FLAG%'
then 'Reject – New, Extension, Replacement invalid entry' --'it is jp'
else ''
end as [ES Fully approved data 1],
case
[Code] ....
View 6 Replies
ADVERTISEMENT
Apr 18, 2008
Hi all......
I have an issue
There are 2 tables... source and target...
Data from source goes into target table under same field...
ISSUE IS -
Data from 'n' no. of records from source table goes into a single record in the target table with delimiter being a semi colon (;)...
where(n >=2)...
For example - if the source table has 'src1', 'src2' and 'src3' as the data then target table will have a single record with semicolon as delimiter as 'src1;src2;src3'
How do we compare the data under this particular field now...
Do we have to use a if then loop for identifying when the ; ends in target data...
kindly help by giving a example...
Thanks,
Avi.
View 1 Replies
View Related
Aug 3, 2007
Ok, here's my setup. I've got a named instance in a SQL 2000 cluster. I only have dbo rights on my database, because it is a shared infrastructure. Here's my current web.config connection string (the meat, anyway):
When I'm at the office, this is my connection string, pretty normal:
connectionString="Data Source=ServerNameInstanceName;Initial Catalog=blah..."
But, when I connect through the VPN, I can't just connect through the named instance - I have a specific port. This is where things get odd.
First, if I try to connect through SQL Server Management Studio (2005), i get nothing. If I try to connect using "ServerNameInstanceName, (comma) Port Number" it connects, but not to my instance. I get a seperate set of databases that I believe are in the default instance. So, I changed the comma to a semicolon (;) - and it still connected to the same thing - connected to the database, but to the wrong set of databases. So, on a whim, I tried plunking my string, which was now "ServerNameInstanceName;(semicolon) PortNumber" into the SQL 2000 Tools and it worked in both Query Analyzer and in Enterprise Manager. So, I thought, I'll just slam this into my connection string and all will be well. No. I can't use a semicolon in my connection string, and I can't find an escape character to use. Double semicolons don't work, a comma doesn't connect me properly, double colons don't work, the JDBC brackets don't work {} - so I'm at a loss. I'm out of ideas. I've set up aliases, and those don't work earlier.
I'm using ASP.net 2 with VB & C# and Visual Studio 2005 Professional. Thanks for any help anyone can give on this!
View 3 Replies
View Related
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
Jun 2, 2014
I am running sqlserver 2012.
My output data is like
COL1
aaa
bbb
ccc
Now, i want to convert the output to
aaa;bbb;ccc
How can i do this?
View 2 Replies
View Related
Jul 28, 2015
I have a hierarchical structure for mapping products to categories, categories go 3 levels deep (depth is defined in articlegroups.catlevel, 0 being the main category and traversing down to lower category level 2). Also, a product may be in more than 1 category(!).
product details are stored in `[products]`
articlegroups are defined in `[articlegroups]`
and the mapping of the products to the articlegroups are defined in `[products_category_mapping]`
Now, I want to retrieve index the full category path for each item, so with the data provided below, I'd expect these 2 rows as a result:
id categorystring
2481446 Taarttoppers > Taarttoppers grap'pig
2481446 Bruidstaart > Taarttoppers > Grappig
Now I can get the separate fields via a statement like this:
SELECT ga.slug_nl as slug_nl_0
FROM articlegroups ga
INNER JOIN products_category_mapping pcm ON pcm.articlegroup_id=ga.id
INNER JOIN products gp on gp.id=pcm.artikelid
WHERE gp.id=2481446
[code]....
View 9 Replies
View Related
Oct 26, 2006
Hi,
I get a string whihc looks like 'Q306/Q406 Version1/Current/Q108 Version2'
I need to split the above string and get each of those values... ' / ' delimiter
Can some one please help on this.
Thanks
View 7 Replies
View Related
Nov 2, 2015
I want to extract two strings from xxxxx - yyyyyy separately as xxxxx and yyyyyy. The source always has two strings brought together with a - symbol. How to extract these two strings.
View 4 Replies
View Related
May 1, 2015
I wish my transform variable, so that adding the OR operator, and the words within the quotes are not to put the OR.
ALTER PROCEDURE
@Product = ' 'ORANGE LEMON' BANANA APPLE 'PEACH PEAR' '
AS
-- I WANT TRANSFORM THE WORDS
@PRODUCT = 'ORANGE LEMON' OR BANANA OR APPLE 'PEACH PEAR'
SELECT Description
FROM Production.ProductDescription
WHERE CONTAINS(Description, @PRODUCT)
GO
View 8 Replies
View Related
Jun 19, 2006
Hi,
Why does this result produce 'Null' and not the expected string of 10 B's?
The var @SecurityString is a VarChar Type.
WHILE @LoopCount <= 10
BEGIN
SET @SecurityString = @SecurityString + 'B'
SET @LoopCount = @LoopCount + 1
END
SELECT @SecurityString AS SecurityCode
Any pointers would be a great help, thanks.
View 3 Replies
View Related
Jan 23, 2004
create procedure ChangePassword(@sUser char(20),@sPassword char(20))
as
begin
execute immediate 'GRANT CONNECT TO ' + @sUser + ' IDENTIFIED BY ' + @sPassword
grant execute on ChangePassword to public
end
I m getting syntax error at '+' sign. I saw in BOL and it is exactly the same.
Can nyone help me out?
Thx
View 2 Replies
View Related
Jan 26, 2004
create procedure CheckSQLErrors( @TheCode integer, @TheState integer, @Routine varchar(40), @Help varchar(40))
as
begin
{ call LogMsg('SQLA',@Routine,@Help,'sqlstate=' + @TheState +
', sqlcode=' + @TheCode) }
end
I m getting this error.
"Incorrect syntax near + "
+ is used for string concatenation. I tried to use CAST to convert
@TheState and @TheCode variables to varchar but did not work. Can you help me out?
FYI
LogMsg is a sproc
create procedure
dbo.LogMsg( @aAppName varchar(18), @aRoutine varchar(20), @aType varchar(5), @aMsg varchar(255))
as
begin
insert into MessageLog(strAppName,strRoutineName,strType,strMe ssage) values(
@aAppName,@aRoutine,@aType,@aMsg)
end
GO
Thks
View 3 Replies
View Related
Feb 21, 2006
I'm trying to concatenate 3 columns into 1. Can someone provide me w/ the syntax?? :) thx
View 1 Replies
View Related
Oct 22, 2007
Hi,
I have a function which takes a string and a delimter. It then splits the string by the delimter and returns a table of resultant strings:
CREATE FUNCTION [dbo].[vs_SplitTags] (@sep char(1), @s varchar(512))
RETURNS table
AS
RETURN (
WITH Pieces(pn, start, stop) AS
(
SELECT 1, 1, CHARINDEX(@sep, @s)
UNION ALL
SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
FROM Pieces
WHERE stop > 0
)
SELECT pn,
SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
FROM Pieces
)
This works very well, other than instances of the delimter are, themselves, considered to be results. For example:
SELECT * FROM vs_SplitTags(' ', 'foo bar') AS result
returns:
pn s
1 foo
2 bar
which is exactly the result I would want.
However,
SELECT * FROM vs_SplitTags(' ', ' foo bar ') AS result -- There are spaces before 'foo' and after 'bar'
returns
pn s
1
2 foo
3 bar
4
And
SELECT * FROM vs_SplitTags(' ', 'foo bar') AS result -- There are two spaces between 'foo' and 'bar'
returns
pn s
1 foo
2
3
4 bar
I want the function to ignore whitespace altogether, be it a single space or multiple spaces. Other than to delimit the boundries between words, of course.
In other words, all three examples above should produce the same result:
pn s
1 foo
2 bar
How can I do this? Any thoughts much appreciated...
View 5 Replies
View Related
Oct 3, 2005
I have the following data:
create table TempTable(name varchar(50), value varchar(50))
insert into temptable values ('A', 'one')
insert into temptable values ('A', 'two')
insert into temptable values ('A', 'three')
insert into temptable values ('B', 'four')
insert into temptable values ('B', 'five')
and i would like the following output:
'A', 'one, two, three'
'B', 'four, five'
any ideas on how to accomplish this in Sql Server 2000?
thx in advance..
View 1 Replies
View Related
Mar 13, 2006
I want to concatanate all text rows returned by the following sql statement:
"SELECT Text FROM PageText WHERE PageTextId = 1"
Table "PageText" has the following columns:
---------------------------------------------
PageTextId (int)
SortOrder (int)
Text (nvarchar(4000))
Is it possible to do this? Kinda like doing a "SUM()" if the values would have been numeric?
View 2 Replies
View Related
Jul 25, 2006
Hi all,
I have a sitaution here where I need to convert some relational data to a flat file. I have a primary record that flatens out pretty well with the exception of two columns that need to have row data converted to strings via concatenation. The column size is Char(146) . I attempted to use 2 cursors to create the strings.
C1 --outside cursor to pull unique record id (161,000+ records)
C2 -- SELECTs the top 29 secondary (relational) records for each C1 rec
(FIELDX as Char(5))
FIELDX is the concatenated up to 29 times and inserted in to a flat table
based on record id for flat file export.
The issue is that this takes FOREVER to run and the 3Ghz XEON w/2GB Ram server weeps.
Declarations are as follows:
DECLARE C1 CURSOR FAST_FORWARD READ_ONLY
FOR
SELECT Distinct Record_ID
FROM tblProcedure
DECLARE C2 CURSOR FAST_FORWARD READ_ONLY
FOR
--need only the top 29 relational records to string out
SELECT TOP 29 Cast(pr_icd1 as Char(5))
FROM tblProcedure WHERE Record_ID = @RECID --From C1
OPEN C2
SET @tmpICDstr = ''
FETCH NEXT FROM C2 INTO @tmpICDchar
WHILE @@FETCH_STATUS = 0
BEGIN
SET @tmpICDstr = @tmpICDchar + @tmpICDstr
FETCH NEXT FROM C2 INTO @tmpICDchar
END
--'INSERT INTO [Validation].[dbo].[tmpICDStr] (RECID, sg) VALUES
(@RECID, @tmpICDstr)
--'INSERT INTO @tmp (RECID, STRsg) VALUES (@RECID, @tmpICDstr)
SET @tmpICDchar = ''
Anybody have a suggestion on how to speed this up. I am looking at about 1min/100 C1 records. Do the math for 161,000+ C1 records. Ugh.
Any suggestion would be appeciated!
tnx
View 4 Replies
View Related
Apr 5, 2006
Hi,How can I remove a part of string from complete string in SQL?this is something i want to do:update aceset PICTURE = PICTURE - '\SHWETABHShwetabhI'But here ' - ' is not allowed.How can I remove the string \SHWETABHShwetabhI from the columnPICTURE?Regards,Shwetabh
View 5 Replies
View Related
Nov 26, 2014
I have a PL/pgSQL Code like this:
[code=" CREATE OR REPLACE FUNCTION public.split_string(text, text)
RETURNS SETOF text
LANGUAGE plpgsql
AS $function$
DECLARE
pos int;
[Code] ....
Its split a String with a delimiter. Like this
[code="select * from split_string('3.584731 60.739211,3.590472 60.738030,3.592740 60.736220', ' ');
"3.584731"
"60.739211,3.590472"
"60.738030,3.592740"
"60.736220""][/code]
My question is how i can save the first result in a temp_array (or table i dont know) so I can get the result and split up the results again with the delimiter ','.
View 1 Replies
View Related
Jul 20, 2005
Dear GroupJust wondered how I can avoid the CHAR(32) to be inserted if @String1 is NULL?SET @String3 = ISNULL(@String1,'') + CHAR(32) + ISNULL(@String2,'')Thanks very much for your expertise and efforts!Best Regards,Martin
View 6 Replies
View Related
Jun 12, 2000
I guess I'm the only one with this problem -- couldn't find anything on it in the back questions. Maybe it's a weird problem. :)
Anyway, although I'm not new to SQL, I am a bit new to stored procedures, and MS SQL Server 7. (I've been using mySQL, decent, but doesn't have many features ... )
I used some ASP and stored procedure code from 4guysfromrolla.com for session tracking through SQL Server.
I've modified most of the stored procedures so that they actually work. :)
The tables it uses are simple:
sessions: sessionid (uniqueidentifier), date_stamp (datetime), sessionipaddr(varchar(50))
sessionvalues: sessionid (uniqueidentifier), sessionvalname (varchar(100)), sessionvaldata (varchar(8000))
To answer some questions before they're asked: It's a resume database, and does need to be able to store 8000 characters at a shot. (I'm hoping 8000 is as large as it gets for this particular field.)
There's only one problem now: One of the stored procedures enters information into the sessionvalue field of the table. However, much of our data contains apostrophes ('), and we need to be able to store them. I thought that modifying the execute statement would do it, something like:
EXECUTE sessiondata '{EC8131F6-409A-11D4-8E88-00A0C9E4F36E}', 'ExpWorkDescs', 'Here' + CHAR(39) + "s some data"
This doesn't work. Indeed, even if the concatenation worked, CHAR(39) doesn't in this context.
Then I thought I'd be really clever, and try a trick from mySQL:
EXECUTE sessiondata '{EC8131F6-409A-11D4-8E88-00A0C9E4F36E}', 'ExpWorkDescs', 'Here's some data'
Naturally, that one didn't work, either. (That was a long shot, admittedly!)
This is mission-critical. Not only apostrophes, but quotes and other punctuation marks must be able to be transferred. Anyone know a way to do it?
View 3 Replies
View Related
Sep 4, 2007
Hi,I'm trying to concatenate a Description (nchar(100)) and Date(datetime) as Description and my initial effort was just"...description+' '+open_date as description..." which throws a date/string conversion error; finally came up with a working string belowbut don't think it's the optimal way to do this - any suggestions?select (rtrim(description)+''+rtrim(convert(char(2),datepart(mm,open_date)))+'/'+convert(char(2),datepart(dd,open_date))+'/'+convert(char(4),datepart(yyyy,open_date))) as description fromoncd_opportunity where opportunity_id=?open_date is not a required field at the db level, but it is requiredon the form so it should not be null as a rule.
View 2 Replies
View Related
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
Sep 26, 2005
I have a table with a field that contains a filter string. I would like to combine the fields into 1 string with an 'OR 'logical operator.
eg.
FilterID 1 FilterString: (PROD.ProductID='10000')
FilterID 1 FilterString: (PROD.ProductID='10001')
Output as 1 line:
(PROD.ProductID='10000') OR (PROD.ProductID='10001')
SELECT FHDR.FilterID, FDET.QString
FROM tblFilters FHDR
INNER JOIN (SELECT QString = ????, FilterID
FROM tblFilterDetails
GROUP BY FilterID) FDET
ON FDET.FilterID = FHDR.FilterID
View 2 Replies
View Related
Jun 11, 2015
I have one table NewsEntities which contains one to many relationship.ie. One NewsId can have multiple EntityId.
Consider,NewsId=1 has two entities with EntityId 1 and EntityId 2 .there multiple newsid which can same or different EntityId .
Now I want to find those news in which EntityId=1 is Present but EntityId=2 is absent
View 7 Replies
View Related
May 16, 2007
Here's a weird one:
We are setting up a job for the SQL Server Agent via SSMS. The Job Step Type is SSIS.
In the Job Step Properties window, on the Set values tab, you can enter Values to override your package variables - normally all well and good.
However in this particular case, the variable Value contains semicolons ( - it is a Connection String for an ODBC driver. Eg: Driver={Client Access ODBC Driver (32-bit)};system=MYSERVER;...
The behaviour for this Value is weird:
If the Value is not surrounded with double quotes ("), the job fails with "The command line parameters are invalid."
If the Value is surrounded with double quotes ("), the job will run as intended. The catch is: that entry and any subsequent "Set Values" entries disappear next time the Job Step Properties window is opened.
This looks like a bug with the parsing of those strings by the Job Step Properties window?
Or am I missing something?
Mike
View 5 Replies
View Related
May 8, 2008
Hi all, mister
I want create a function but I get this error: Error Invalid use of side-effecting or time-dependent operator in 'EXECUTE STRING' within a function
I think in a function, cannot use temp tables, or calling exec or store procedures.
Which is the best solution for my issue ? develop store procedure ??
thanks.
CREATE FUNCTION fnObtenerTablaMaestra ()
RETURNS @T Table ( Descripcion VARCHAR(20) NOT NULL, CIF VARCHAR(8) NULL )
AS
BEGIN
DECLARE @cmd nvarchar(max)
DECLARE @sql nvarchar(max)
DECLARE @nexoUNION NVARCHAR(max)
DECLARE @params nvarchar(max)
DECLARE @NombreTabla VARCHAR(MAX)
DECLARE @Descripcion VARCHAR(MAX)
DECLARE @CIF VARCHAR(MAX)
-- Cannot access temporary tables from within a function.
-- IF EXISTS (SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '#tmpTable%')
-- DROP TABLE #tmpTable
-- CREATE TABLE #tmpTable ( Descripcion VARCHAR(20) NOT NULL, CIF VARCHAR(8) NULL )
SET @nexoUNION = NULL
DECLARE c1 CURSOR for
SELECT [CD_NOMBRE_TABLA], [DS_CAMPO_DESCRIPCION], [DS_CAMPO_CIF] FROM [TABLA_MAESTRA]
OPEN c1
FETCH c1 INTO @NombreTabla, @Descripcion, @CIF
--FETCH NEXT FROM c1 INTO @NombreTabla, @Descripcion, @CIF
WHILE @@FETCH_STATUS >= 0
--WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sql =
'INSERT INTO #tmpTable
N'''+ @NombreTabla + '''
N'''+ @Descripcion + '''
N'''+ @CIF + ''''
SELECT @sql =
'SELECT ' + @Descripcion + ', '+ @CIF + ' FROM ' + @NombreTabla
IF @nexoUNION IS NULL
BEGIN
SET @nexoUNION = 'UNION'
END
ELSE
SET @sql = @nexoUNION + ' ' + @sql
-- EXECUTE (@sql)
--Exec(@sql)
FETCH c1 INTO @NombreTabla, @Descripcion, @CIF
--FETCH NEXT FROM c1 INTO @NombreTabla, @Descripcion, @CIF
END
CLOSE c1
DEALLOCATE c1
--SET @sql = 'SELECT Descripcion, CIF FROM #tmpTable'
-- Error Invalid use of side-effecting or time-dependent operator in 'EXECUTE STRING' within a function
EXECUTE(@sql)
RETURN
END
GO
View 1 Replies
View Related
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
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
Apr 8, 2014
I have a string and i want to get only the numbers from right.
For example if I have the string Like '123756zxfggr123456' then it will show me only 123456 or if i have the string like
'4vbz67xfggr123dfd' then it will show me only 123 or if i have the string like '123756zxfgg43r5' then it will show me only 5.
I got a function where it gives me all the numbers in a string but I don't need that
CREATE FUNCTION dbo.udf_GetNumeric
(@strAlphaNumeric VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @intAlpha INT
[Code] ....
If I ran the select statement it gives me the result 111123456 but i want only 123456 or if i select
SELECT dbo.udf_GetNumeric('111zxfggr6587fhhfkwee') AS 'Num' it will show me 6587.
View 8 Replies
View Related
May 2, 2014
I have log files that I am loading into a table with duration data in the format "xx hrs xx min xx sec". Only the parts that are required will be there so if duration is only 2 seconds , the column will show "2 sec".
I am trying to get the duration into in to do some analysis on it and I have come up with this query so far which returns the correct data but i am wondering if there is a way to do what I am trying in a more readable format.
CREATE TABLE #tmp(duration VARCHAR(20))
INSERT INTO #tmp
VALUES ('1 hrs 20 min 12 sec'), --4812 sec
('48 sec'), --48 sec
('39 min 1 sec'), --2341 sec
('11 hrs 1 min 1 sec'), --39661 sec
('59 min 0 sec'), --3540 sec
('2 min 50 sec') --170 sec
And this is what I have so far
SELECT CASE
WHEN CHARINDEX('hrs', duration, 1) <> 0 THEN CAST(SUBSTRING(duration, 1, CHARINDEX(' hrs', duration, 1) - 1)AS int) * 3600 + CAST(SUBSTRING(duration, CHARINDEX(' hrs', duration, 1) + 5, CHARINDEX(' min', duration, 1) - 7)AS int) * 60 + CAST(SUBSTRING(duration, CHARINDEX(' min', duration, 1) + 5, CHARINDEX(' sec', duration, 1) - 14)AS int)
WHEN CHARINDEX('hrs', duration, 1) = 0
[Code] ...
I ultimately plan on converting this to a SSIS expression so that is why I am looking to simplify it.
View 3 Replies
View Related
Aug 18, 2014
I need extracting string that is between certain characters that are in certain position.
Here is the DDL:
DROP TABLE [dbo].[StoreNumberTest]
CREATE TABLE [dbo].[StoreNumberTest](
[StoreNumber] [varchar](50) NULL,
[StoreNumberParsed] [varchar](50) NULL)
INSERT INTO [dbo].[StoreNumberTest]
[Code] ....
What I need to accomplish is to extract the string that is between the third and fifth '-' (dash) and insert it into the StoreNumberParsed while eliminating the fourth dash.
Sample output would be:
KY117
CA132
OH174
MD163
FL191
I know that parse, charindex, patindex all might come in play, but not sure how to construct the statement.
View 5 Replies
View Related
Jan 8, 2015
I am trying to make a query that will group my errors messages together - my problem is that each of the error messages is unique, due to them having an unique id in them.
"GROUP BY LIKE '%ThePartToGroupBy%'"
View 9 Replies
View Related
