One problem is the two columns can be of different data types, like a date col and a text time. If anyone knows how to bring these into a single column using SSIS, I would love to know since I have been battling with it for a while.
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.
I need to concatenate two date fields so they appear in a drop-down list like this: 8:00 AM - 10:00 AM I'm using MS SQL 2005 and my query looks like this: SELECT ClinicTimesID, ClinicID, (CTStartTime + ' - ' + CTEndTime) AS TimeSlot FROM Clinics_Times WHERE (ClinicID = 1) and I get this error: Msg 242, Level 16, State 3, Line 1 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. Can I strip out the date so that only the time appears or is it even possible to concatenate these fields? If so, how? Thanks!
Hello all, I'm trying to combine two columns of data into a third column using a formula on the thrid column. Each of the columns could contain nulls and each of the columns could contain padding after or before the data. I'm trying to use the following formula yet SQL is throwing an error. Can someone provide another set of eyes to check this out? ISNULL(LTRIM(RTRIM([user_Define_4a])),’’) + ISNULL(LTRIM(RTRIM([user_Define_1])),’’) Thanks
I am completely new to SQL and I have some syntax questions. I am trying to concatenate 4 fields and some padded constants to form a new key field to perform joins. The result should be a twelve character field without spaces. My problem is that the current fields use spaces as place holders and I need to replace the spaces with ‘0’.
1st ‘0’ (constant) 2nd, 3rd, and 4th, from [RD_ID] (without the suffix) 5th and 6th from [RD_ID] suffix or replace spaces with ‘00’ 7th 1 or 2 from [RDWY_ID] 8th Z from [MLGE_TYPE] or replace space with ‘0’ 9th 1 – 9 from [OVLAP_MLGE_CD] or replace space with ‘0’ 10th ‘S’ (constant) 11th ‘0’ (constant) 12th ‘0’ (constant)
Results should resemble 0001CQ100S00 or 000100100S00
Col1 Col2 Col3 --------------------------------------------------------------------------- Andrews S 93845877712 P Sylvia 9999876669 J Bill K 7657654677 L
I need the output like this Col1 Col2 Col3 -------------------------------------------------------------------------- AndrewsS 93845877712 P Sylvia 99999876669 J BillK 76576546677 L
The character on the left of Col2 has to be joined to Col1 and character on the right of col2 has to be joined to Col3. Can anybody suggest a query for this.
Hi All, I've been trying to create a dynamic query using the 'Like' clause and '%'. my code snippet looks like this: while (@@FETCH_STATUS = 0)begin set @likeString = @likeString + ' item_Text LIKE ''%'+@word+'%'' OR ' fetch next from theLike into @word end
-- strip off last ORset @likeString = ltrim(rtrim(substring(@likeString, 0, (len(@likeString) - 3))))-- ================================================-- create query to find keywords in the index and store in temp table-- ================================================set @query = 'INSERT into #resulttable (itemcount, item_id) SELECT COUNT(d.item_id), d.item_id FROM tp_index_details AS d INNER JOIN tp_index ON d.idx_id = tp_index.idx_id 'set @query = @query +' WHERE (d.idx_id IN (SELECT idx_id FROM tp_index AS i WHERE ( 'set @query = rtrim(@query) + @likeStringset @query = @query + ' ) AND (subscription_id = 1000))) GROUP BY d.item_id ORDER BY d.item_id DESC' The problem is the @query string gets truncated. My question is how to get the quotes around '%' variables to work in a string? Thanks for any help! regards Davej
Hi all - I have posted inquiries on this rather vexing issue before, so I apologize in advance for revisting this. I am trying to create the code to add the parameters for two CheckBoxLists together. One CheckBoxList allows users to choose a group of Customers by Area Code, the other "CBL" allows users to select Customers by a type of Category that these Customers are grouped into. When a user selects Customers via one or the other CBL, I have no problems. If, however, the user wants to get all the Customers from one or more Area Codes who ALSO may or may not be members of one or more Categories; I have had trouble trying to create the proper SQL. What I have so far:Protected Sub btn_CustomerSearchCombined_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_CustomerSearchCombined.Click Dim CSC_SqlString As String = "SELECT Customers.CustomerID, Customers.CustomerName, Customers.CategoryID, Customers.EstHours, Customers.Locality, Category.Category FROM Customers INNER JOIN Category ON Customers.CategoryID = Category.CategoryID WHERE " Dim ACItem As ListItem Dim CATItem As ListItem For Each ACItem In cbl_CustomersearchAREA.Items If ACItem.Selected Then CSC_SqlString &= "Customers.AreaCodeID = '" & ACItem.Value & "' OR " End If Next CSC_SqlString &= "' AND " <-- this is the heart of my problem, I believe For Each CATItem In cbl_CustomersearchCAT.Items If CATItem.Selected Then CSC_SqlString &= "Customers.CategoryID = '" & CATItem.Value & "' OR " End If Next CSC_SqlString = Left(CSC_SqlString, Len(CSC_SqlString) - 4) CSC_SqlString &= "ORDER By Categories.Category" sql_CustomersearchGrid.SelectCommand = CSC_SqlString End SubAny help on this is much appreciated, many thanks --
Hi, I'm trying to mak emy query dynamic such that depending upon certain conditions, the conditions in the WHERE clause change. I have my SP as shown below and it doesn't seem to work correctly this way and seems like it is not even taking it as a condition. Please advise on what is going wrong here. I'm building the @Condition variable dynamically and appending it to the where clause below. Any help wud be greatly appreciated.
Thanks
CREATE PROCEDURE dbo.CabsSchedule_ViewSchedule ( @SiteCode smallint = 0, @YearMonth int = NULL, @ByYearMonth bit = 1 ) AS
DECLARE @tempYearMonth int DECLARE @Condition varchar(1000) SET @tempYearMonth = 0
IF @YearMonth IS NULL OR @YearMonth = 0 BEGIN EXECUTE CabsSchedule_GetYearMonth @tempYearMonth, @YearMonth OUTPUT END
IF @ByYearMonth = 0 BEGIN DECLARE @Year int DECLARE @Month int SET @Year = CAST(SUBSTRING(CAST(@YearMonth AS VARCHAR(6)),1,4) AS INT) SET @Month = CAST(SUBSTRING(CAST(@YearMonth AS VARCHAR(6)),5,2) AS INT) SET @Condition = ' DATEPART ([month], FullDate) = ' + CAST(@Month AS VARCHAR(2)) + ' AND DATEPART ([year], FullDate) = ' + CAST(@Year AS VARCHAR(4)) + ' AND ' END ELSE BEGIN SET @Condition = ' YearMonth = ' + CAST(@YearMonth AS VARCHAR(6)) + ' AND ' END
SELECT BillPeriod = CASE WHEN BillPeriod = 32 THEN 'NB' WHEN BillPeriod = 33 THEN 'Holiday' ELSE Convert(nVarChar(7), BillPeriod) END, WorkDay = CASE WHEN WorkDay = -1 THEN '' WHEN WorkDay = 0 THEN 'Holiday' ELSE Convert(nVarchar(7), WorkDay) END, JulianDate, CalendarDay, CalDayBillRcvd, Remarks, FullDate FROMdbo.CabsSchedule WHERE YearMonth = @YearMonth AND SiteCode = @SiteCode GO
I am trying to concatenate two columns First_Name and Last_Name to display as Name in a View. I used the following statement but the result only shows the First_Name.
Select First_Name + Last_Name as Name from Address;
I have an instance where I need to concatenate some data that is stored in a text datatype. I can't cast it to a varchar/char because that may well truncate the data. I just read about UPDATETEXT, which I think I can use, but I need to use it for a bunch or rows and it looks like this works on one row at a time. Anyone have experience with this?
I am using the following sql statement to concatenate fields from a sql server in my query.
SELECT RTRIM(title) + ' ' + RTRIM(fname) + ' ' + RTRIM(lname) AS name, id FROM contact
2 questions:
1. How can I avoid a Null name field resulting from either fname or lname being Null? One Null field in the contatenation yields a Null field, even though the other field is not Null.
2. Does concatentation in the sql statement reduce performance significanlty?
I am using concatenation in Query in Sql Server like,
Select Column1 + ' bla bla ' + Column2 as MyColumn from MyTable
So, here any secruity issure occur or not.... because some one tell to me.. d'not use Concetenation in query bcz it is not secure, worst in performance and helpfull in SQL injection....... any idea about that ??
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?
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
Hi, i have this query that is perfect on SQL2005 but in SQL2000 it gives me the error: "Column 'D.RevenueCode' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."
SELECT SUBSTRING(D.RevenueCode, 5, 2) + ' ' + (SELECT A.sdp_des FROM vw_UNI_se_girev A WHERE A.sdp = SUBSTRING(D.RevenueCode, 5, 2) AND Cast(A.Comp AS Integer) = D.CompanyCode) AS RevenueGrpItem,
SUM(nSign * isNull(ItemAmount, 0)) AS GrandTotal
FROM SIC_Invoice_Header AS H, vw_SIC_Invoice_Detail AS D WHERE H.CompanyCode = 1 AND H.StockCategory = 'INV' AND H.CompanyCode = D.CompanyCode AND H.StockCategory = D.StockCategory AND H.SaleType = D.SaleType AND H.TransactionType = D.TransactionType AND H.SerialNo = D.SerialNo AND D.RevenueCode is not NULL
GROUP BY D.CompanyCode, SUBSTRING(D.RevenueCode, 5, 2) ORDER BY SUBSTRING(D.RevenueCode, 5, 2)
It appears that when I remove the + and put a comma it works correctly but that's not what I want !
Hello,Using SQL SERVER 2000I have 4 columns with varchar(80) each that I want to concatenate.When I look at the result, it only gives me 256 characters. What am Imissing on my code?Select Cust_Number, Info = convert(varchar(1000),rtrim(line1) +char(13)+rtrim(Line2) + char(13)+ rtrim(line3) + char(13)+rtrim(line4))[color=blue]>From tableOne[/color]GoThank you for your input.Edgar
Are we actually going to get a string concatenation aggregate in SQL Server 2008?? I am so sick of having to write a UDF for every concatenation I want to do. I am well aware of all the methods available to do it now, and they are all junk, and the performance is horrible. I'm sure it is a challenging problem, and there are known pitfalls, but its time to address these.
The notion that it is the responsibility of the front end to do the concatenation is pure bs. You don't dump 10,000 rows to your report server when you only need to display 1000, just so you can concatenate one lousy column.
I want to be able to write:
SELECT CustName, SUM(ProductPrice), CONCAT(ProductName, [my separator], ORDER OVER ProductName ASC) FROM Orders
And I'm not going to stop complaining til I get it! (-;
My table looks like this. Col1 Col2 ------------------------------------------------- Andrew 1 0394588893 Charles 6 0495869457 Carmon 5 9878957874
But, I want the result like this. Col1 Col2 --------------------------------------------------- Andrew 10394588893 Charles 60495869457 Carmon 59878957874
The last digit in the col1 has to be joined as the first digit in the col2. How to write this query?.
I have a table called long_text that stores long descriptions used for different projects. It contains fields key1-8, (key1, key2 etc) whcih key1 is used to seperate and organize different descriptions and key2 is the primary key which holds the unique ID of the projects. Then there is 2 more fields one called line_number and one called line_text. line_text holds the actual descriptions and line_number is the sequential number of the line text. Line_text is set at varchar(4000).
The issue i am getting, is that in my report i made all the joins to get it to call the line_text field for a comments section on one of my reports. However, The report fails if the user is writing their comments more than 70 characters. Ive found what is happening is that if the comment is over 70 characters, the database creates a new record, keeps key1, key2 the same, adds one to line_number and then uses the next 70 characters of the comment in line_text. And will repeat the process untill the comment is over. So if say the comments section is 700 characters, i will end up with 10 records. And it looks something like this.
Here is the specific line of code to call teh commnets section
(select line_text from ip.structure q, ip.long_text l, ip.notes n where q.structure_code = n.structure_code and n.structure_code = l.key2 and l.key2 = pe.planning_code and key1 = 'PE_NOTES01') "Comments"
And its failing because im returning more than 1 record. Does anyone know any way around this.. I dotn really understand why its creating a new record and using line_number to sort it... esp when line_text can hold 4000 characters... Is there a way i can concatenate the other records where line_number is not 1? Any help much appreciated.
hay all, i'm really new to stored procedures ..and i have the problem: in my stored procedure : ALTER PROCEDURE dbo.selectFromView @Year as varchar(10), @Country as varchar(10), @Family as varchar(10), @Manu as varchar(10), @Status as varchar(10), @Type as varchar(10), @Operator as varchar(10)ASSELECT * FROM ViewofAllWHEREProductionYear = @Year andCountryName = @Country andFamily = @Family andManufacturer = @Manu andStatus = @Status andType = @Type andOperatorName = @OperatorRETURN i don't wanna use the equality (=) after the WHERE, because the equality is already in the parameter ..so i want it like this (concatenation), but it doesn't work. ProductionYear & @Year & how should i write it?? Thanks in advance.
hi everyone i am writing a stored procedure and i am new in this field i want to concatenate a string, i got an error hope someone could help. my stored procedure is: CREATE PROCEDURE dbo.ProdCatComp ( @Product nvarchar(40), ) AS DECLARE @str nvarchar(100) SET @str='Products.ProductName like '%' + @Product + '%''
SELECT Products.ProductName, Products.UnitPrice, Categories.CategoryName, Suppliers.CompanyName, Suppliers.ContactName, Suppliers.HomePage FROM Products INNER JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID WHERE Products.ProductName<>'' AND @str GO
The error is : Error 403: Invalid operator for data type. Operator equals modulo, type equals varchar.
