Hot answers tagged

57

If you are using SQL Server you can use the REVERSE() function to check? SELECT CASE WHEN @string = REVERSE(@String) THEN 1 ELSE 0 END AS Palindrome; Including Martin Smith's comment, if you are on SQL Server 2012+ you can use the IIF() function: SELECT IIF(@string = REVERSE(@String),1,0) AS Palindrome;


17

Since there are a fair number of solutions I'm going to go with the "critique" part of your question. A couple of notes: I've fixed some typos and noted where I did. If I'm wrong about them being a typo mention it in the comments and I'll explain what's going on. I'm going to point out several things that you may already know, so please don't take offense ...


15

You could also use a Numbers table approach. If you don't already have an auxiliary numbers table you can create one as follows. This is populated with a million rows and so will be good for string lengths up to 2 million characters. CREATE TABLE dbo.Numbers (number int PRIMARY KEY); INSERT INTO dbo.Numbers (number) SELECT TOP 1000000 ...


12

There are several flaws to this approach: The term "preview" can be quite misleading in most cases, depending on the nature of the data being operated on (and that changes from operation to operation). What is to ensure that the current data being operated on will be in that same state between the time the "preview" data is gathered and when the user comes ...


11

A couple of other options are to just rename the tables, or if they have clustered indexes, you can disable the clustered index.


11

The REVERSE() method "improved", i.e. reversing only half of the string: SELECT CASE WHEN RIGHT(@string, LEN(@string)/2) = REVERSE(LEFT(@string, LEN(@string)/2)) THEN 1 ELSE 0 END AS Palindrome; I don't expect anything weird to happen if the string has an odd number of characters; the middle character doesn't have to be ...


10

The biggest difficulty in coming to a precise solution is in defining exactly what characters are to be included (or excluded, whichever direction makes more sense for the operation). Meaning: Are we talking about VARCHAR / ASCII data or NVARCHAR / Unicode data? The list of punctuation characters for ASCII data depends on the Code Page which in turn ...


10

This should work for you: CREATE VIEW MyView AS SELECT <colList> FROM <TableOne> INNER JOIN <TableTwo> ON ... AND ..... AND ..... LEFT JOIN <TableThree> ON ... AND ..... AND ..... WHERE ..... AND ..... AND ..... Then replace in Procs with: ... FROM MyView WHERE MedicalPlanCode IN ('abc', 'def', 'ghi')


9

This recursive CTE (SQL Fiddle) should work with your sample: WITH cte(ParentID) AS( SELECT ParentID FROM @Instances WHERE [Part] = 'Rivet' UNION ALL SELECT i.ParentID FROM cte c INNER JOIN @Instances i ON c.ParentID = i.InstanceID WHERE i.ParentID > 0 ) SELECT ParentID, count(*) FROM cte GROUP BY ParentID ORDER BY ParentID ; ...


9

The UCS-2 encoding is always 2 bytes per character and has a range of 0 - 65535 (0x0000 - 0xFFFF). UTF-16 (regardless of Big Endian or Little Endian) has a range of 0 - 1114111 (0x0000 - 0x10FFFF). The 0 - 65535 / 0x0000 - 0xFFFF range of UTF-16 is 2 bytes per character while the range above 65536 / 0xFFFF is 4 bytes per character. Windows and SQL Server ...


8

Without using REVERSE, which is what immediately comes to mind, but still using a function1; I would construct something like the following. This part simply removed the existing function, if it already exists: IF OBJECT_ID('dbo.IsPalindrome') IS NOT NULL DROP FUNCTION dbo.IsPalindrome; GO This is the function itself: CREATE FUNCTION dbo.IsPalindrome ( ...


8

One way would be to pivot WITH CTE AS ( SELECT [Document No_], 'SL' AS Source FROM [Sales Line] UNION ALL SELECT [Document No_], 'SLA' AS Source FROM [Sales Line Archive] ) SELECT P.[Document No_], P.SL, P.SLA FROM CTE PIVOT (COUNT(Source) FOR Source IN ([SL], [SLA])) P WHERE P.[Document No_] IN (1,2,3); I'd hope that the predicate on ...


7

New Answer (based on new details provided in updates to the Question) One thing to consider is that a Primary Key and a Clustered Index are not the same thing. A Primary Key is a constraint and deals with the rules by which the data lives (i.e. data integrity); it has nothing to do with efficiency / performance. A Primary Key requires that the key column(s) ...


6

This is an inline TVF-friendly version of Martin Smith's set-based solution, additionally decorated with a couple of superfluous enhancements: WITH Nums AS ( SELECT N = number FROM dbo.Numbers WITH(FORCESEEK) /*Requires a suitably indexed numbers table*/ ) SELECT IsPalindrome = CASE WHEN EXISTS ( SELECT * FROM ...


6

Without using REVERSE... It's always fun to use a recursive solution ;) (I did mine in SQL Server 2012, earlier versions might have limitations on recursion) create function dbo.IsPalindrome (@s varchar(max)) returns bit as begin return case when left(@s,1) = right(@s,1) then case when len(@s) < 3 then 1 else ...


6

Is there any other way to achieve the same(tables cannot be used anymore) without dropping them? A schema change is a very fast operation - just metadata change is required. The original idea I got was from Aaron Bertrand's blog - Schema Switch-A-Roo. You can follow the steps from my answer here Obviously there are other methods like sp_rename N'old ...


5

Ranges in the pattern syntax use the sorting rules of your collation. Use a binary collate clause so the range is ordered by character code. (I also changed it to LIKE as I find that more obvious than PATINDEX > 0) SELECT * FROM mbrnotes WHERE LINE_TEXT LIKE '%[' + CHAR(127)+ '-' +CHAR(255)+']%' COLLATE Latin1_General_100_BIN2


5

Remove the permissions on the table from the Role(s)/ Group(s)/ Account(s) that [might] be using it. If anything blows up, put them back [quickly]. Hint: Using a script to do make these changes would be a really, really Good Idea.


5

I may be over-simplifying this a bit but, if we say that punctuation is all that is left when alphanumeric values are removed, then the following will search for strings that have non-alphanumeric characters in them. Create Table #Test ( Value VarChar(10) ) Insert Into #Test Values ('123a'), ('456b'), ('12ABC'),('AB!23'),('C?D789') -- Original Select ...


4

A major issue you're going to run into is that with any value greater than 1, LEFT or RIGHT will return multiple characters, not the character at that position. If you wanted to keep with this method of test, a really simple way to modify it would be RIGHT(LEFT(String,@n),1)=LEFT(RIGHT(String, @StringLength),1) This will always grab the rightmost ...


4

I've set up option 3, Master Server, a number of times, but never for scheduling backups. I'm able to schedule jobs for performance data collection, pushing out administrative changes/script updates via SQLCMD, and run tests against multiple versions of SQL Server on multiple instances to test for any unexpected changes made after SP/CU application (among ...


4

The simplest approach is often the best and I don't really have that much of an issue with code duplication in SQL, especially not in the same module. After all the two queries are doing different things. So why not take 'Route 1' or Keep It Simple and just have two sections in the stored proc, one to simulate the work you need to do and one to do it, eg ...


4

Just for fun, here's a SQL Server 2016 Scalar User-Defined function with the In-Memory OLTP feature: ALTER FUNCTION dbo.IsPalindrome2 ( @inputString NVARCHAR(500) ) RETURNS BIT WITH NATIVE_COMPILATION, SCHEMABINDING AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English') DECLARE @i INT = 1, @j INT = LEN(@inputString) ...


4

You can use a pattern like this: SELECT files.database_id, db.name AS DatabaseName, STUFF((SELECT ', ' + names.name FROM sys.master_files names WHERE names.database_id = files.database_id FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)') , 1, 2, '') AS NameList FROM sys.master_files files ...


4

SQL Server will automatically add a 4 byte integer uniqueifier to all duplicate (ie second and subsequent for a key value) clustered index keys to make it unique. This is not exposed to the user though. The advantage of adding your own unique identifier as a secondary key column is that you can then still seek by companyid but also seek to individual rows ...


3

select author from article where author IS NOT NULL and author <> '' except select name from author since any comparison the null is false I think below works select author from article where author <> '' except select name from author


3

The output from the CASE can only be of one data type. The first branch is decimal and the second is nvarchar(255). According to the Data Type Precedence (Transact-SQL) rules in SQL Server the resulting data type for the CASE is decimal. Add one more cast to nvarchar(255) in the first branch of the CASE. SELECT CASE WHEN [Parcel Number 1] like '%E+%' ...


3

Well, I did something similar to Steve, but with no need for a #temp table or recursive CTE. DECLARE @BigString nvarchar(max) = N' /* CREADO POR : Wxxwww wwwwww */ /* FECHA CREACIÓN : 10/12/2015 */ /* DESCRIPCIÓN : ...


3

Looks like a GROUP_CONCAT to me, unless I'm missing something, eg CREATE TABLE #heredity ( parent INT NOT NULL, child INT NOT NULL, depth INT NOT NULL, CONSTRAINT _pk_heredity PRIMARY KEY ( parent, child ) ); GO INSERT INTO #heredity ( parent, child, depth ) VALUES ( 1, 1, 0 ), ( 2, 2, 0 ), ( 3, 3, 0 ), ( 1, 2, 1 ), ( ...


2

I've used Openrowset a number of times for this task. This code will create a table in SQL. SELECT * INTO EXCEL_IMPORT FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0; Database=C:\Excel\Spreadsheet.xls; HDR=YES; IMEX=1', 'SELECT * FROM [Sheet1$]'); Ideally you want to create the table first and then use INSERT INTO instead of the SELECT INTO. ...



Only top voted, non community-wiki answers of a minimum length are eligible