I'm trying to join Table1 and Table2 on the Url fields. However all the Urls in Table2 end with a "/" as per the example below: **Table1** ╔═════════════════════╗ ║ Url1 ║ ╠═════════════════════╣ ║ http://site1.com ║ ║ http://site2.com ║ ║ http://site3.com ║ ║ http://site4.com ║ ║ http://site5.com ║ ╚═════════════════════╝ **Table2** ╔═════════════════════╗ ║ Url2 ║ ╠═════════════════════╣ ║ http://site1.com/ ║ ║ http://site2.com/ ║ ║ http://site3.com/ ║ ║ http://site4.com/ ║ ║ http://site5.com/ ║ ╚═════════════════════╝ I'm using a SUBSTRING to remove the final character from the Url2 field. This is what my query looks like: SELECT Table1.Url1, SUBSTRING(Table2.Url2, 1, LEN(Table2.Url2) - 1) AS Urlx FROM Table2 LEFT JOIN Table1 ON Urlx = Table1.Url1 However I cannot get the **Urlx** field in the LEFT JOIN clause to resolve. Is it possible to join tables on a manipulated field or have I constructed my query incorrectly?

Employee_M

City		Emp_Num_ID
Cleveland	2164445437/FTTD/JAMES, TYLER/139561151
Tokyo       1261120379/FTTD/BOYD, ADAMS/14468140  
Marakech	4049838896/FTTD/SMITH, TULY E/13956144
Houston	    7980151429/FTTD/NEARY, HARTMAN/14215411

I'm trying to extract all digits after the third / with substring select substr(Emp_Num_ID,/,10) as Emp_ID from Employee_M sometimes we can have between 4 to 10 charterers after the third / so I chose 10 for the length. any idea how to fix that.

I am seeing some behavior that I cannot figure out. We have a sproc that joins a bunch of tables and filters down some pipe-separated (|) values in a column. We cross apply a string splitter (I believe it is very much based on Jeff Moden's splitter) to separate the values. We then use the following to pull out the part we want to see based on the position of a tilde, concat some text to it and then wrap it in brackets. QUOTENAME( substring(tr.TableVar, 1, charindex('~', tr.TableVar) - 1) +'_someText' ) The issue that we started to see was an error "Invalid length parameter passed to the LEFT or SUBSTRING function." This would normally be pretty obvious to me: there's a value that does not contain a tilde. *However*, I have verified that every single record this query would return does, in fact, contain a tilde. This is both with an without any filtering done. I verified this with my eyeballs, with searching for any value that has 0 as the charindex value for the tilde, and a literal search for any record that doesn't contain a tilde (no records returned). What makes this weirder to me is that when I remove the QUOTENAME function, there is no error. I can also take the values and place them in a temp table, run the same select on those and it produces no error. I have also tried adding a replace to use search for a caret instead of a tilde (as below)... no error QUOTENAME( substring(tr.TableVar, 1, charindex('^', replace(tr.TableVar,'~','^')) - 1) +'_someText' ) I'm kind of grasping at straws at this point. Sure, we can just use the replace method but we would also like to know *why* we're now experiencing this error (this never happened before). Is there some kind of Unicode character issue with a tilde in the QUOTENAME function? Am I even on the right track? FWIW, we are on Azure SQL and are using collation SQL_Latin1_General_CP1_CI_AS. FYI and example (in fact the *only* value in this DB) looks like this after the split string (no quotes) 'DOG-1~3~_na_~'. Thanks in advance for your time. EDIT: I just went back and removed the joins to check the values without join conditions. There are indeed values that exist without the tilde. I do know that there are some cases when the engine isn't going to follow it's logical order (from, where, group by, etc) so it can effectively try to apply the substring before the filters. However, that leaves me wondering why using QUOTENAME makes that happen, and especially why using a replace doesn't throw the error; my thought would be that would cause the same issue. As always, thanks in advance

I've a database value 'INS. Company Cancelled' where multiple values are separated by '.' I'm trying to replace 'INS' with 'INSEXP' my expected result is 'INSEXP. Company Cancelled' I've tried below two queries to update the field my output is like 'INSEXP. Company Cancelled. Company Cancelled' update my_table SET column_name = (select replace(column_name, 'INS', 'INSEXP') from my_table WHERE seq_num = 123) WHERE seq_num = 123; update my_table set column_name = replace(column_name, 'INS', 'INSEXP') WHERE seq_num = 123; Could someone please tell me what I'm doing wrong here?

I need to remove the last character of my subquery but I cannot figure out how to reference my subquery in the subselect as I need the length of it. The subselect collects all rows and converts them into a single string by appending them with a separator ";". This is what I need: ``` (SUBSTRING( (SELECT d1.name +'; ' FROM data1 d1 inner join data2 d2 on d1.id = d2.id WHERE d1.id = dOfOutterSelect.id ORDER BY d1.CreatedOn FOR XML PATH('') ), LENGTH(),1)) As "Row name",

I have a text column (*emailbody*) in a table (*notification*) which keeps a record of the HTML that went out for a particular email. Somewhere inside that HTML file, there is a line of text that contains the following line: Delayed ${shipmentId} for Carrier Where ${shipmentId} represents the specific Id that was generated for that particular record. Example: **12345** Basically I want to query the column in the table for all the shipmentId values, which unfortunately will involve getting the string positions between Delayed and for Carrier. I understand this will likely involve using one of Postgres' regular expression functions but I'm not quite sure how to do it, or if there's something a little simpler.

I have a varchar(max) column that holds a JSON request string. I need a SQL statement for a report that will select all of the instances of that substring. More specifically I need to grab the LoanId Value from that substring entry pulling a result set that contains a row for each LoanId. Any help would be greatly appreciated. A considerably abbreviated JSON string containing the stuff I'm looking for. [ {"loanId":"1111111111","someotherValue":7}, {"loanId":"2222222222","someotherValue":4}, {"loanId":"3333333333","someotherValue":5}, ]

Using SQL server 2008, is there a way to perform SELECT query in a view replaces a row containing comma-separated values with their corresponding text value from another table? STRING_SPLIT and STRING_AGG is not available in 2008 version. **EDIT:** Added create and insert script CREATE TABLE Data( Id int, Value1 varchar(50) NULL, Value2 int NULL, Value3 datetime ) GO CREATE TABLE CodeValue( Id int, Code varchar(50) NULL ) GO INSERT [dbo].[Data] ([Id], [Value1], [Value2], [Value3]) VALUES (1, N'0;1;2', 43, CAST(N'2020-07-09T00:00:00.000' AS DateTime)) GO INSERT [dbo].[Data] ([Id], [Value1], [Value2], [Value3]) VALUES (2, N'0;2;3', 652, CAST(N'2020-07-03T00:00:00.000' AS DateTime)) GO INSERT [dbo].[Data] ([Id], [Value1], [Value2], [Value3]) VALUES (3, N'2', 1234, CAST(N'2020-07-02T00:00:00.000' AS DateTime)) GO INSERT [dbo].[CodeValue] ([Id], [Code]) VALUES (0, N'Apple') GO INSERT [dbo].[CodeValue] ([Id], [Code]) VALUES (1, N'Orange') GO INSERT [dbo].[CodeValue] ([Id], [Code]) VALUES (2, N'Banana') GO INSERT [dbo].[CodeValue] ([Id], [Code]) VALUES (3, N'Dogmeat') GO Consider my view contains data from two tables; Data and CodeValue, that would look like this: Data Id | Value | Value2 | Value 3 ============================== 1| 0;1;2| some other data 2| 0;2;3| 3| 2 | CodeValue Id | Code ============= 0| Apple 1| Orange 2| Banana 3| Dogmeat So the actual output from the SELECT query in my view would be: View Id | Value ============ 1| Apple, Orange, Banana 2| Apple, Banana, Dogmeat 3| Banana I've messed around with stored procedures and functions, but can't wrap my head around those and how to actually implement this. **EDIT 2:** Tried using STUFF() using the following template: WITH CTE_TableName AS ( SELECT FieldA, FieldB FROM TableName) SELECT t0.FieldA , STUFF(( SELECT ',' + t1.FieldB FROM CTE_TableName t1 WHERE t1.FieldA = t0.FieldA ORDER BY t1.FieldB FOR XML PATH('')), 1, LEN(','), '') AS FieldBs FROM CTE_TableName t0 GROUP BY t0.FieldA ORDER BY FieldA; However I can't seem to join codeValues on split values using homebrew split_string function: CREATE FUNCTION dbo.tvf_SplitString (@stringToSplit VARCHAR(100)) RETURNS @returnList TABLE(Id VARCHAR(5)) AS BEGIN DECLARE @splitValue VARCHAR(5) DECLARE @post INT WHILE CHARINDEX(';', @stringToSplit) > 0 BEGIN SELECT @pos = CHARINDEX(';', @stringToSplit) SELECT @splitValue = SUBSTRING(@stringToSplit, 1, @pos-1) INSERT INTO @returnList SELECT @splitValue SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit -@pos) END INSERT INTO @returnList SELECT @stringToSplit RETURN END

I need to trim the last seven characters off of all the cells in a column. This query returns the results that I need SELECT test_date, SUBSTRING(test_date FROM 1 FOR CHAR_LENGTH(test_date) - 7) as test from results; I now need to take the computed result from the substring and replace the original values. Here's what the data looks like, if it helps.