sql-server

Mixture of CHARINDEX with SUBSTRING if on SQL Server. DECLARE @Text VARCHAR(100) = ‘The cat is under the table.’ DECLARE @CharToSearch CHAR = ‘s’ DECLARE @CharactersToRetrieve INT = 5 SELECT CharacterPosition = CHARINDEX(@CharToSearch, @Text), TrimmedString = SUBSTRING( @Text, CHARINDEX(@CharToSearch, @Text) + 1, @CharactersToRetrieve) Result: CharacterPosition TrimmedString 10 unde

CREATE FUNCTION [dbo].[fnSplitString] ( @string NVARCHAR(MAX), @delimiter CHAR(1) ) RETURNS @output TABLE(splitdata NVARCHAR(MAX) ) BEGIN DECLARE @start INT, @end INT SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) WHILE @start < LEN(@string) + 1 BEGIN IF @end = 0 SET @end = LEN(@string) + 1 INSERT INTO @output (splitdata) VALUES(SUBSTRING(@string, @start, @end – @start)) SET … Read more

If number1 is a value from first row and number2 is value from next row, then use ROW_NUMBER(): ;WITH cte AS ( SELECT col, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as rn FROM (VALUES (-0.75640390000), (0.58303570000), (-0.07794400000), (0.00000000000), (0.58303570000), (-0.07794400000), (0.42976550000), (0.58781540000), (0.00909080000), (0.58781540000) ) as t(col) ), rec AS ( SELECT CAST((1+col/100) as float) … Read more

declare @input char(4) set @input = 4218 SET DATEFIRST 1 declare @wk int set @wk = cast(SUBSTRING(@input,1,2) as int) declare @yr int set @yr = 2000 + cast(SUBSTRING(@input,3,2) as int) select dateadd (week, @wk, dateadd (year, @yr-1900, 0)) – 4 – datepart(dw, dateadd (week, @wk, dateadd (year, @yr-1900, 0)) – 4) + 1

You have aliased two columns as published. a.published, and CASE a.type WHEN N’component’ THEN a.published+2*(e.enabled-1) WHEN N’url’ THEN a.published+2 WHEN N’alias’ THEN a.published+4 WHEN N’separator’ THEN a.published+6 END AS published,

Database tables are unsorted by nature. There is no last row in the table. If there is a clustered index, The records are stored in the same order as the clustered index, but that does not mean that’s the order of the rows – Unless you specify an order by clause in your query, no … Read more

Let’s try creating a self-contained example – the below code works fine for me. Can you insert some sample data from your application into this toy code and see if it runs as you expect? If it does, then the problem lies elsewhere in your code. CREATE TABLE #TempReceivingUnmatchedUPCs ( RecUPC VARCHAR(20), RecSupplierInvoiceNumber VARCHAR(20), Foobar … Read more