I would like to concatenate a String value to an Int value using an SQL statement. At the moment it reads like this:
SELECT 'website.com/shop/product.cfm?ProductID=' + Products.ProductID AS Product_URL
But unfortunately I am getting the error:
"Conversion failed when converting the varchar value 'website.com/shop/product.cfm?ProductID=' to data type int."
Any idea how to get around this at all just using an SQL query statement?
Please help me with this if you can. I have one table with CustomerID and some other data. In other table i have CustomerID(the link with the first table) and Agent The relation of the first with the second one is ONE TO MANY. I want something like this: Customer,'Agent1,Agent2,Agent3'
Hi all I need some help in concatenatng a string in T-SQL. Having used the Command Microsoft Access inside the 'SQL View' window and typed the following it worked perfectly.
Code Snippet
UPDATE tblValidUsers SET blocked_users = blocked_users + 'name_123@hotmail.com;' WHERE userid='Onam'
However attempting the same command in T-SQL I get the following error:
Msg 403, Level 16, State 1, Line 1Invalid operator for data type. Operator equals add, type equals text.
Reason for having this command is I want to be able to add something to the end of the field "blocked_users" without actually overwriting the fields contents.
So for instance if I had the items: "Item1, Item2, Item3" in blocked_users and I updated it with "Item4" then the value "Item4" would be added to the end thus the use of "+" is used to concatenate. Is there a way of doing this in T-SQL?
As I build a record set in an SP I need to add in a string containing a list derived from a second query. I need the results of the sub query to be presented as a single string in the first query, separated by a single space.
I have no idea how to do this in t-sql, and am doing it on the web server at the moment, but becase the dataset is quite large, I'm getting 20 - 40 second processing times which is far too long.
I have found reference to the xp_sprintf function but this is not supported by my host, so not a solution.
I'm no expert in t-sql, so I imagine there's a way somewhere, and would be grateful for any advice available.
To start, I am NOT a SQL programmer. I have to do some minimal SQL administration (DB Creation, Backups, Security) on spatial databases that are for the most part managed by a 3rd party program. My experience with T-SQL is mostly simple tasks (i.e. Select and Update statements)..However I have been requested to calculate an ID Field using the values of two other fields. Per the request, I need to convert one field to Hex and concatenate with the second field.
ex. Field 1 + Field 2(hex string) = Field 3 Field 1 = 'FF02324323' Field 2 = 'Smith Creek' Field 3 = 'FF02324323536D69746820437265656B'
Field 1 VarChar(10) (Code) Field 2 VarChar(65) (Common Name) Field 3 VarChar(max) (ResourceID)
Spent half the day searching and have tried various forms of CAST, CONVERT, fn_varbintohexstr and others but have unable to come up with the correct combination to get what I need.
I've been trying to return hex data in a way that can be concatenated. I need the actual hex info (e.g. 0x6E3C070) as displayed since it contains info about the path to a file. So I can turn it into D:6E3C 7 to get the path to the file. In searching around I have come across a way to do this but can't figure out how to get it to run through a column and either display or insert into a table multiple results.
-Here's the user function that converts an integer into a hex string-
CREATE FUNCTION udf_hex_string (@i int) RETURNS varchar(30) AS BEGIN DECLARE @vb varbinary(8) SET @vb = CONVERT(varbinary(8),@i) DECLARE @hx varchar(30) EXEC master..xp_varbintohexstr @vb, @hx OUT RETURN @hx END GO
--'PageStoreId' contains the data that needs to be converted into the editable hex string --'HexString' is where I'd like it to go so I can parse it later.
--I can run the below select and get the hex string. But am stuck on how to run a select or update that would run through the 'XPages.PagestoreId' column and insert the hex string into the 'XPages.Hexstring' column. 'XPages.PagestoreId' could have 100's of entries that need to be converted and placed in the relevant the 'XPages.Hexstring' column.
The following query works perfectly (returning all words on a list called"Dolch" that do not contain a form of "doing"):SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWordFROM dbo.Dolch LEFT OUTER JOINdbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,'FORMSOF(INFLECTIONAL, "doing")')WHERE (dbo.CombinedLexicons.vchWord IS NULL)However, what I really want to do requires me to piece two strings together,resulting in a word like "doing". Any time I try to concatinate strings toget this parameter, I get an error.For example:SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWordFROM dbo.Dolch LEFT OUTER JOINdbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,'FORMSOF(INFLECTIONAL, "do' + 'ing")')WHERE (dbo.CombinedLexicons.vchWord IS NULL)I have also tried using & (as in "do' & 'ing") and various forms of singleand double quotes. Does anyone know a combination that will work?FYI, in case this query looks goofy because of the unused "CombinedLexicons"table, it is because the end result should be a working form of thefollowing...Figuring out the string concatination is just a step toward this goal:SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWordFROM dbo.Dolch LEFT OUTER JOINdbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,'FORMSOF(INFLECTIONAL, ' + dbo.CombinedLexicons.vchWord + ')')WHERE (dbo.CombinedLexicons.vchWord IS NULL)Thanks!
col1 Â Â Â col2 Â Â Â col3 1 Â Â Â Â Â 0 Â Â Â Â Â 0 1 Â Â Â Â Â 0 Â Â Â Â Â 1 1 Â Â Â Â Â 1 Â Â Â Â Â 1 0 Â Â Â Â Â 1 Â Â Â Â Â 0
I am expecting output asÂ
col1 Â Â Â col2 Â Â Â col3 Â Â Â NewCol 1 Â Â Â Â Â 0 Â Â Â Â Â 0 Â Â Â Â Â Â SL 1 Â Â Â Â Â 0 Â Â Â Â Â 1 Â Â Â Â Â Â SL,PL 1 Â Â Â Â Â 1 Â Â Â Â Â 1 Â Â Â Â Â Â SL,EL,PL 0 Â Â Â Â Â 1 Â Â Â Â Â 0 Â Â Â Â Â Â EL
condition if col>0 then SL else '', Â if col2>0 EL else '', if col3>0 PL else ''
I have a large table that looks like this. (ID INT NOT NULL IDENTITY(1,1),PK INT , pocket VARCHAR(10)) 1, 1, p12, 1, p23, 2, p34, 2, p45, 3, p56, 3, p67, 4, p78, 5, p19, 5, p210,5, p83 i would like to loop through the table and concatenate the pocket filed for all the records that has the same pk. and insert the pk and the concatenated string into another table in a timely manner. can anyone help? i have to use temporary tables. (not cursors-with cursors i know how to di it, but i want with temporary table) thanks in advance
i would like to loop through the table and concatenate the pocket filed for all the records that has the same pk. and insert the pk and the concatenated string into another table in a timely manner.
ACCOU NAME     NAME TODATE                           ID    EDUCAT   EXPIRYDATE 011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-basis 1900-01-01 00:00:00.000 011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-basis 2016-06-24 00:00:00.000 011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-VOL 2018-02-11 00:00:00.000
Need to get it like
ACCOU NAME     NAME TODATE                           ID    EDUCAT   EXPIRYDATEstring 011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-basis 1900-01-01 00:00:00.000 2016-06-24 00:00:00.000 011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-VOL 2018-02-11 00:00:00.000
In other words I need to Aggregate the 2 dates and concatenated into a new string col string so basically a sum with a group by but instead of a sum I need to concatenate the string. I know this should be possible using stuff and for xml path but I can't seem to get my head around it everything I try concatenates all the strings, not just the appropriate ones.
Hi, I need to concatenate a string with an int variable on a stored procedure; however, i looks like i am lost in single and double quotes. Does any one know the right comination of quotes for this please? My Code is below: 1 @Price int 2 3 DECLARE @SqlPrice varchar(50) 4 5 if (@Price is not null) 6 7 set @sqlPrice = 'AND price' + '' > '' + '' + @Price + '' 8
is it possible to concatenate 2 recordsets ? i have 2 select, and i need then to be displayed one after other.using union all the results are gathered, ordered like only one select.thanks for all
Hi, I am trying to write some simple SQL to join two fields within a table, the primary key is an int and the other field is a varchar. But i am receiving the error:'Conversion failed when converting the varchar value ',' to data type int.The SQL I am trying to use is:select game_no + ',' + team_name as matchfrom result Thanks
I'm trying to use a variable (@sqlfields1) for building a select statement. Can someone please help me because the variable isn't set correctly when I'm trying to concatenate it with itself (the "@sqlfields1"-variable is declared as "char(50)":
If @showo1 = 1 select @sqlfields1 = 'o1,'
If @showo5 = 1 select @sqlfields1 = @sqlfields1 + 'o5'
Just like the title asks...Is it possible...I have a table that stores memos but breaks the memo text field up and assigns a key to associate it all as one big text field in the application. What I am needing to do is to create a custom table for a customization to have the memo txt in one row per customer, per memo number. So, is there any way to concatenate memotext into one row per memo?
Is there a way to concatenate the values of a particular field together into one field for all the records in each group of a GROUP BY without using XML?
Ideally, I want to accomplish this in a stored procedure. Normally, I would handle this in the front end, but my front end is Crystal ...
For example, if my data was: 1 "A" 1 "B" 1 "C" 2 "D" 2 "E"
I want to group by the first value and concatenate the second value: 1 "A,B,C" 2 "D,E"
Very easy using code - I simply add a function to the query in Access (not very efficient, but effective), or using recordsets in the app (which is actually very efficient) except my front end is Crystal in this case .
-- Prepare sample data DECLARE@Sample TABLE (ID INT, Code VARCHAR(3))
INSERT@Sample SELECT290780, 'LT' UNION ALL SELECT290780, 'AY' UNION ALL SELECT290781, 'ILS' UNION ALL SELECT290780, 'AY'
-- Show the expected output SELECT DISTINCTs1.ID, STUFF((SELECT DISTINCT TOP 100 PERCENT ',' + s2.CODE FROM @Sample AS s2 WHERE s2.ID = s1.ID ORDER BY ',' + s2.CODE FOR XML PATH('')), 1, 1, '') AS CODES FROM@Sample AS s1 ORDER BYs1.ID
SELECT DISTINCTs1.ID, STUFF((SELECT TOP 100 PERCENT ',' + s2.CODE FROM @Sample AS s2 WHERE s2.ID = s1.ID ORDER BY ',' + s2.CODE FOR XML PATH('')), 1, 1, '') AS CODES FROM@Sample AS s1 ORDER BYs1.ID
SELECT DISTINCTs1.ID, STUFF((SELECT ',' + s2.CODE FROM @Sample AS s2 WHERE s2.ID = s1.ID FOR XML PATH('')), 1, 1, '') AS CODES FROM@Sample AS s1 ORDER BYs1.ID
I am beggining to learn stored procedures and I am trying toconcatenate the where clause below but I keep getting an error:declare @sqlwhere varchar(15)set @sqlwhere = 'parentid=2'select * from categories where @sqlwhereThis is the error I am gettingServer: Msg 170, Level 15, State 1, Line 3Line 3: Incorrect syntax near '@sqlwhere'.What am I doing wrong?ThanksRod
hi.Trying to concatenate two columns:select uname+' '+uaddress as NameAdr from tblUserI only get the first field, name!!??The datatype is both nvarchar. 100 and 50 chars long.If I run this query:select uname, uaddress from tblUsertheres no problem.Any suggestions?ThanksSWN
I have a big problem, as I commented in other thread in this forum I Have a DataReader with an MDX query which right now runs out of memory, but I figured that if I made the query by range I could get what I need without running out of memory. However, to achieve that I need to concatenate at the end of a file with a Flat File destination, the result of every query, but I don't know how to do it, It always writes over the data.
Date ID Name Job Number JobType 12/12/2007 123456 Fred Smith 111111 Full Day 12/12/2007 654321 Bob Blue 222222 Half Day AM 12/12/2007 654321 Bob Blue 333333 Half Day PM
I need the following output: Date ID Name Job Number JobType 12/12/2007 123456 Fred Smith 111111 Full Day 12/12/2007 654321 Bob Blue 222222 Half Day AM 12/12/2007 654321 Bob Blue 333333 Half Day PM
Now before you say the output is the same . It isn't! There are only 2 records in the output. The italic lines are one record, with a carriage return linefeed between each piece of data. So for job number the field is equal to 111111 + CHAR(10) + CHAR(13) + 222222
Could someone please point me in the right direction?
Hi All, I have a very simple question which I can't seem to find an easy way of doing in SSIS. (Alternatively I may be having a "Can't see the wood for the trees" moment )
I have a folder where files are deposited on a regular basis . (Every few seconds)
I want to take those files and concatenate them into a single file in another directory ready for processing.
The equivelent command prompt line would be copy c: estfiles*.* c: estfilescatteddata.dat
How would I be best doing this in SSIS? Also I could do with working out the best way to quiesce the folder so I don't get any locked files errors.
Is there any way of concatenating 2 fields and a seperator, an id (integer) then "|||" then a nvarchar field, it would make my life much easier if I could come up with SQL that works in ms access as well as sql server.. Thanks
