locked
How to remove all even characters from a string??? RRS feed

  • Question

  • How can I make: 'Deoxyribonucleic acid'

    into 'Doyiouli cd'  

    What string functions would I use?  How can I make it so it will do this to any variable with any character string value stored in it?

    Saturday, October 5, 2019 4:03 PM

Answers

  • How can I make: 'Deoxyribonucleic acid'

    into 'Doyiouli cd'  

    What string functions would I use?  How can I make it so it will do this to any variable with any character string value stored in it?

    Check this:

    declare @txt nvarchar(MAX) = N'Deoxyribonucleic acid'
    
    ;with MyCTE as (
    	SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    	from sys.all_objects t1
    	-->> If you have a long string and not many objects
    	-->> Then you can add the following line in ordert to get more rows
    	--CROSS JOIN sys.all_objects tt
    )
    select STRING_AGG(substring(@txt, RN, 1),'')
    FROM MyCTE
    WHERE RN <= LEN(@txt) and RN % 2 = 1
    Note! if you have a numbers table (which you should) then you do not need the CTE which simply used here to create a virtual Numbers table on the fly


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]



    • Edited by pituachMVP Saturday, October 5, 2019 6:02 PM
    • Marked as answer by soldierfc Tuesday, October 8, 2019 12:54 AM
    Saturday, October 5, 2019 5:58 PM
  • Hi,

    SQL Server 2014 or less:

    create function RemoveEven(@str varchar(200)) returns varchar(200) as
    begin
    declare @i int = 1, @str2 varchar(200) = ''
    while (@i<=len(@str))
    begin
    if (@i%2 = 1)
       set @str2 = @str2 + substring(@str,@i,1)
    set @i = @i + 1
    end
    return (@str2)
    end

    select dbo.RemoveEven('Deoxyribonucleic acid')

    Doyiouli cd

    Hi ,

    Splitting the text into characters will work on previous versions. The only part in my solution above which is related to SQL Server 2016 and above is the use of the function STRING_AGG in order to combine the characters back to one string after removing the even characters.

    It's probably make no sense to use a loop for this task. You can use the same approach as mu original answer to split the characters and you use FOR XML to combine them together as one string.

    In general, for most cases we should avoid using loops in tabular databases.

    -- If it is simple a text without special non-URL characters like space or <
    -- then this is a simple solution
    declare @txt nvarchar(MAX) = N'Deoxyribonucleicacid'
    ;with MyCTE as (
    	SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    	from sys.all_objects t1
    	--CROSS JOIN sys.all_objects tt
    )
    SELECT substring(@txt, RN, 1)
    FROM MyCTE
    WHERE RN <= LEN(@txt) and RN % 2 = 1
    for XML PATH('')
    GO
    
    -- The problem is that FOR XML also encode the result to fit URL encoding -- If there is only spaces then we can use simple replace -- space converted to &#x20; when we use FOR XML declare @txt nvarchar(MAX) = N'Deoxyribonucleic acid' ;with MyCTE as ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN from sys.all_objects t1 --CROSS JOIN sys.all_objects tt ) select REPLACE( (SELECT substring(@txt, RN, 1) FROM MyCTE WHERE RN <= LEN(@txt) and RN % 2 = 1 for XML PATH('')) , '&#x20;' ,' ' ) GO -- To deal with all non-URL characters we can use one of the URLDecode function online. Google can fins some nice functions.


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    • Marked as answer by soldierfc Tuesday, October 8, 2019 12:54 AM
    Sunday, October 6, 2019 2:36 PM
  • Hi,

    Check the below Query - Table variable method for doing the same logic for more data sets and SubQuery method used instead of CTE method.

    As Ronen States, logic is Same,just it is another alternative!

    DECLARE @SampleTest AS TABLE (ID INT IDENTITY(1,1) NOT NULL,Samples NVARCHAR(MAX));
    
    INSERT INTO @SampleTest(Samples)
    VALUES ('Deoxyribonucleic acid')
    ,('Deoxyribonucleic acid 1234');  --Another variable added for Testing
    
    SELECT ID,Samples,STRING_AGG(SUBSTRING(Samples, R_no, 1),'') AS Expected_Result
    FROM @SampleTest,((
    	SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) R_no FROM sys.all_objects t 
    	--,sys.all_objects t1  
    )) a
    WHERE R_no <= LEN(Samples) and R_no % 2 = 1
    GROUP BY ID,Samples
    Please mark this as answer if it solved your issue. 
    Please vote this as helpful if it helps to solve your issue. 

    Thanks,
    Arulmouzhi

    • Marked as answer by soldierfc Tuesday, October 8, 2019 12:54 AM
    Saturday, October 5, 2019 7:31 PM
  • Hi,

    SQL Server 2014 or less:

    create function RemoveEven(@str varchar(200)) returns varchar(200) as
    begin
    declare @i int = 1, @str2 varchar(200) = ''
    while (@i<=len(@str))
    begin
    if (@i%2 = 1)
       set @str2 = @str2 + substring(@str,@i,1)
    set @i = @i + 1
    end
    return (@str2)
    end

    select dbo.RemoveEven('Deoxyribonucleic acid')

    Doyiouli cd
    • Marked as answer by soldierfc Tuesday, October 8, 2019 12:54 AM
    Sunday, October 6, 2019 9:43 AM
  • -- kv
    
    DECLARE @input_txt AS VARCHAR(MAX)
    DECLARE @output_txt AS VARCHAR(MAX) =''
    
    SET @input_txt  ='Deoxyribonucleic acid';
    -- output 
    
    ;with vv AS 
    (
      SELECT  1  AS n  ,  LEN(@input_txt) AS max_ln
      UNION ALL
      SELECT n+1 , max_ln   FROM vv WHERE n+1 <=  max_ln 
    )
     SELECT   @output_txt = @output_txt + SUBSTRING( @input_txt   ,  n ,1 )
    		FROM vv AS o WHERE n %2 <> 0  order by 1 ; 
     SELECT @output_txt 
      



    • Edited by msbi_Dev Sunday, October 6, 2019 2:26 PM
    • Marked as answer by soldierfc Tuesday, October 8, 2019 12:54 AM
    Sunday, October 6, 2019 2:24 PM
  • Hi soldierfc,

     

    Or please try following script.

     
    declare @txt nvarchar(MAX) = N'Deoxyribonucleic acid'
    
    ;with MyCTE as (
    SELECT number RN FROM master..spt_values WHERE type='p'  
    )
    select STRING_AGG(substring(@txt, RN, 1),'')
    FROM MyCTE
    WHERE RN <= LEN(@txt) and RN % 2 = 1
    

    Please kindly mark the helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by soldierfc Tuesday, October 8, 2019 12:55 AM
    Monday, October 7, 2019 2:37 AM

All replies

  • How can I make: 'Deoxyribonucleic acid'

    into 'Doyiouli cd'  

    What string functions would I use?  How can I make it so it will do this to any variable with any character string value stored in it?

    Check this:

    declare @txt nvarchar(MAX) = N'Deoxyribonucleic acid'
    
    ;with MyCTE as (
    	SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    	from sys.all_objects t1
    	-->> If you have a long string and not many objects
    	-->> Then you can add the following line in ordert to get more rows
    	--CROSS JOIN sys.all_objects tt
    )
    select STRING_AGG(substring(@txt, RN, 1),'')
    FROM MyCTE
    WHERE RN <= LEN(@txt) and RN % 2 = 1
    Note! if you have a numbers table (which you should) then you do not need the CTE which simply used here to create a virtual Numbers table on the fly


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]



    • Edited by pituachMVP Saturday, October 5, 2019 6:02 PM
    • Marked as answer by soldierfc Tuesday, October 8, 2019 12:54 AM
    Saturday, October 5, 2019 5:58 PM
  • Hi,

    Check the below Query - Table variable method for doing the same logic for more data sets and SubQuery method used instead of CTE method.

    As Ronen States, logic is Same,just it is another alternative!

    DECLARE @SampleTest AS TABLE (ID INT IDENTITY(1,1) NOT NULL,Samples NVARCHAR(MAX));
    
    INSERT INTO @SampleTest(Samples)
    VALUES ('Deoxyribonucleic acid')
    ,('Deoxyribonucleic acid 1234');  --Another variable added for Testing
    
    SELECT ID,Samples,STRING_AGG(SUBSTRING(Samples, R_no, 1),'') AS Expected_Result
    FROM @SampleTest,((
    	SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) R_no FROM sys.all_objects t 
    	--,sys.all_objects t1  
    )) a
    WHERE R_no <= LEN(Samples) and R_no % 2 = 1
    GROUP BY ID,Samples
    Please mark this as answer if it solved your issue. 
    Please vote this as helpful if it helps to solve your issue. 

    Thanks,
    Arulmouzhi

    • Marked as answer by soldierfc Tuesday, October 8, 2019 12:54 AM
    Saturday, October 5, 2019 7:31 PM
  • Hi,

    SQL Server 2014 or less:

    create function RemoveEven(@str varchar(200)) returns varchar(200) as
    begin
    declare @i int = 1, @str2 varchar(200) = ''
    while (@i<=len(@str))
    begin
    if (@i%2 = 1)
       set @str2 = @str2 + substring(@str,@i,1)
    set @i = @i + 1
    end
    return (@str2)
    end

    select dbo.RemoveEven('Deoxyribonucleic acid')

    Doyiouli cd
    • Marked as answer by soldierfc Tuesday, October 8, 2019 12:54 AM
    Sunday, October 6, 2019 9:43 AM
  • -- kv
    
    DECLARE @input_txt AS VARCHAR(MAX)
    DECLARE @output_txt AS VARCHAR(MAX) =''
    
    SET @input_txt  ='Deoxyribonucleic acid';
    -- output 
    
    ;with vv AS 
    (
      SELECT  1  AS n  ,  LEN(@input_txt) AS max_ln
      UNION ALL
      SELECT n+1 , max_ln   FROM vv WHERE n+1 <=  max_ln 
    )
     SELECT   @output_txt = @output_txt + SUBSTRING( @input_txt   ,  n ,1 )
    		FROM vv AS o WHERE n %2 <> 0  order by 1 ; 
     SELECT @output_txt 
      



    • Edited by msbi_Dev Sunday, October 6, 2019 2:26 PM
    • Marked as answer by soldierfc Tuesday, October 8, 2019 12:54 AM
    Sunday, October 6, 2019 2:24 PM
  • Hi,

    SQL Server 2014 or less:

    create function RemoveEven(@str varchar(200)) returns varchar(200) as
    begin
    declare @i int = 1, @str2 varchar(200) = ''
    while (@i<=len(@str))
    begin
    if (@i%2 = 1)
       set @str2 = @str2 + substring(@str,@i,1)
    set @i = @i + 1
    end
    return (@str2)
    end

    select dbo.RemoveEven('Deoxyribonucleic acid')

    Doyiouli cd

    Hi ,

    Splitting the text into characters will work on previous versions. The only part in my solution above which is related to SQL Server 2016 and above is the use of the function STRING_AGG in order to combine the characters back to one string after removing the even characters.

    It's probably make no sense to use a loop for this task. You can use the same approach as mu original answer to split the characters and you use FOR XML to combine them together as one string.

    In general, for most cases we should avoid using loops in tabular databases.

    -- If it is simple a text without special non-URL characters like space or <
    -- then this is a simple solution
    declare @txt nvarchar(MAX) = N'Deoxyribonucleicacid'
    ;with MyCTE as (
    	SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    	from sys.all_objects t1
    	--CROSS JOIN sys.all_objects tt
    )
    SELECT substring(@txt, RN, 1)
    FROM MyCTE
    WHERE RN <= LEN(@txt) and RN % 2 = 1
    for XML PATH('')
    GO
    
    -- The problem is that FOR XML also encode the result to fit URL encoding -- If there is only spaces then we can use simple replace -- space converted to &#x20; when we use FOR XML declare @txt nvarchar(MAX) = N'Deoxyribonucleic acid' ;with MyCTE as ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN from sys.all_objects t1 --CROSS JOIN sys.all_objects tt ) select REPLACE( (SELECT substring(@txt, RN, 1) FROM MyCTE WHERE RN <= LEN(@txt) and RN % 2 = 1 for XML PATH('')) , '&#x20;' ,' ' ) GO -- To deal with all non-URL characters we can use one of the URLDecode function online. Google can fins some nice functions.


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    • Marked as answer by soldierfc Tuesday, October 8, 2019 12:54 AM
    Sunday, October 6, 2019 2:36 PM
  • MS SQL Server is not the ONLY database. Most database softwares in current world have vast mechanisms supporting loops and myriad other programming extensions that defies small subset of features offered by MS SQL Server. Its high time Microsoft give up on ANSI stuff and extend MS SQL Server to match with 20th century (yes 20th not 21st 'cause they are so far behind the curve) or get EXTINCT :) I feel like preaching to the wrong choir.
    ok jokes aside here is my beef with you folks offering solutions. Most of the solutions you offer are not portable. My solutions using loops are portable. Always try to provide solutions that are portable that MAY require small changes pertaining to specific languages. XML is not portable. 

    Although I have beef with ANSI but where do I go without ANSI left join? I mean I don't want to get into Oracle using + but overloading of functions etc just scratching the surface what is happening in 21st century. Welcome to the real world. 




      

    Sunday, October 6, 2019 3:10 PM
  • Its high time Microsoft give up on ANSI stuff

    If Microsoft would implement the full ANSI spec for SQL, that would be a tremendous improvement for all T-SQL workers out there. To wit, there are many goodies that we are missing out on:
    * Row constructors.
    * Full implementation of windowed aggregates.
    ' RESPECT NULLs for LEAD/LAG.
    * Row-pattern recognition.
    * The INTERVAL data type.
    Only to name a few.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Sunday, October 6, 2019 6:40 PM
  • Most of the solutions you offer are not portable. My solutions using loops are portable. Always try to provide solutions that are portable that MAY require small changes pertaining to specific languages. XML is not portable. 

    I totally disagree with you, but this is open discussion :-)

    For most cases...
    I prefer something that works over something that follows theoretical rules.
    I prefer performance over something that does not fit the tools you choose to use.
    I prefer to choose the right tools in advance and not trying to use something that does not fit the tools you chose.
    I think that "portable" is less important then the result!

    The fact is simple - For most cases using loops in SQL Server will result in poor performance!
    So you can tell your client that you used "portable" since you have "principles". You can ask your clients to pay more for resources and you can explain to them that they will get poor performance if they will work with you, since you like "portable" solutions. I don't think that they will like it. I will probably prefer to offer them a solution which behave better over "portable" poor solution in most cases, if this is the choice I need to do :-)

    I totally agree with Erland regarding implement the full ANSI spec of functionality!

    I do not care that it is a standards, but I do care that these functionality (which unfortunately Microsoft did not implemented) are powerful for getting results and for our work.

    * By the way, I remember multiple threads in the old connect system regarding these options, which I think Microsoft did not moved to the new UserVoice system, and this is sad. There were suggestions with many many votes, and I am not sure if and where they are in the new UserVoice system. I think that Itzik ben-gan opened some of the connects regarding these topics


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    • Edited by pituachMVP Monday, October 7, 2019 2:03 AM
    Monday, October 7, 2019 1:58 AM
  • Hi soldierfc,

     

    Or please try following script.

     
    declare @txt nvarchar(MAX) = N'Deoxyribonucleic acid'
    
    ;with MyCTE as (
    SELECT number RN FROM master..spt_values WHERE type='p'  
    )
    select STRING_AGG(substring(@txt, RN, 1),'')
    FROM MyCTE
    WHERE RN <= LEN(@txt) and RN % 2 = 1
    

    Please kindly mark the helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by soldierfc Tuesday, October 8, 2019 12:55 AM
    Monday, October 7, 2019 2:37 AM
  • I don't care about ANSI and I don't know why anyone has to follow any of it any shape or form. Its just total waste of time and useless. The organization itself is just meaningless and obviously except Microsoft no other database vendors care about it. Having said that I would like to see MS SQL offering constructs like multi dimensional arrays and myriad other low level programming constructs even pointers if necessary! Everyone has already got out of the hang up of sticking to set based SQL and Microsoft is still stuck on it (case in point no 1 database vendor Oracle from day one). Basically developers need to have all the weapons (again non set based low level constructs) available in his arsenal.

    As for loops causing performance issue someone give me a break. Do you think underneath your ms sql engine there are no loops? Its just all low level manipulation and constructs doing all that manipulation and set based solution is first thing out of the window when db engine process user queries. Everyone should take a class on assembly language (its been long time for me) so that you know what goes on underneath.







    • Proposed as answer by Soumen Barua Monday, October 7, 2019 3:16 AM
    • Edited by Soumen Barua Monday, October 7, 2019 3:22 AM
    • Unproposed as answer by pituachMVP Monday, October 7, 2019 4:52 AM
    Monday, October 7, 2019 2:57 AM
  • I don't care about ANSI

    It's funny that you first talk about being portable, and next you say that you don't care about ANSI...

    The organization itself is just meaningless and obviously except Microsoft no other database vendors care about it.

    That is not my perspection. In my previous post I mentioned row-pattern recognition. This is not a widely implemented feature, but it exists in Oracle - and it has existed longer in Oracle than in the ANSI standard. It is no wild guess that Oracle worked to get into the standard - which they would do if they care about.

    And Postgres appears to the ambition to implement what is the standard.

    As for loops causing performance issue someone give me a break. Do you think underneath your ms sql engine there are no loops? Its just all low level manipulation and constructs doing all that manipulation and set based solution is first thing out of the window when db engine process user queries. Everyone should take a class on assembly language (its been long time for me) so that you know what goes on underneath.

    It is correct that inside the SQL Server engine there are loops. That does not mean that your loops are just as good as the loops inside the engine. When running loops it is important to avoid overhead, and it is important to put the loops as far in as you can. When you loop yourself, you add a lot of overhead that is repeated with every statement.

    For the particular problem at hand, a loop makes sense if is only a single value, but if we need to do this operation on a million values, it is a lot better to split up the strings in table format and re-aggregate them into strings, because pushes the loop over the table inside the engine.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, October 7, 2019 9:49 PM
  • <<That is not my perspection. In my previous post I mentioned row-pattern recognition. This is not a widely implemented feature, but it exists in Oracle - and it has existed longer in Oracle than in the ANSI standard. It is no wild guess that Oracle worked to get into the standard - which they would do if they care about. And Postgres appears to the ambition to implement what is the standard.>>

    I don't agree because whenever I look at a any Oracle programming structure first thing I notice is that they will do everything to break away from ANSI.  Just because you say something does not mean that it is truth apart from the fact that Oracle does not follow any standard because they think THEY are the STANDARD and definitely not ANSI or any other body.

    As for loops, this is the first time I heard about efficient vs inefficient loops. Its just a loop and ms db engine will use multiple nested loops judging from MS SQL history. Having said that if loop is so bad then why offer it as part of the language, just discontinue it? There is a reason for everything think about it. You mention about 'if there are millions of row', there is no end to 'what if' analysis because then we all have to just stop eating and die instead. What if there is not, just one row in the table? There is no silver bullet ok? The user posed a question and there was ONLY one string, he didn't say he will process million of such strings and why would I assume that he got millions of rows? I don't think it is smart. 




    Tuesday, October 8, 2019 12:25 AM

  • I am sorry but it make no sense to me (same as preview response which I chose to not respond like saying that you "don't care about ANSI"), and it seems without any base, and I do not think that we will get to an agreement here this way. I have a bad feeling that you will disagree what we will say, with continues attempt to prove that you are right. Therefore, let me put aside the original discussion about "loops" and simply give some general points from life.

    >> don't care about ANSI

    This is very bad IMO and it says a lot about this discussion. As Erland said It's funny that you first talk about being portable but don't care about ANSI now. By the way, standards has huge value! I never said that standards are something we should ignore or that it is not important. I said that regarding these improvement points the functionality is what I care. I only say that standards are tools and not the goal. You do not have to use any existing tool for any task but you should understand what tools are available and use them when they fit in the way that they fit best.

    >> Oracle does not follow any standard because they think THEY are the STANDARD and definitely not ANSI or any other body.

    1. I am not sure that you understand what American National Standards Institute (ANSI) is, since Oracle's people are part of ANSI😃

    Do you how much money and effort Oracle as a company (and Oracle's people as individuals) put into ANSI directly or to their activities? DO you know how much effort Oracle (and SQL Server teams as well) put into attempt to follow standards (which does not mean they do it fully, but they sure do not TRY to "break away")?!? 

    I suggest you to go to ANSI website and search the word "oracle" in their search box.

    Do you know who is Donald R. Deutsch for example?!? Donald is a Vice President at Oracle... and you know something more... Donald is a director on the board of the ANSI... and this is only one example.

    2. How do speak in the name of Oracle?!?

    Are you part of Oracle? Do you have any internal information? Or at least, are you directly connect to the people that develop Oracle and you get this from them?

    Point aside: I am not familiar with what is your connection to SQL Server teams or Oracle, but Erland and myself do have the direct personal contact with the people that develop the server and we have internal information which we cannot discuss in public as we both signed on non-disclosure agreement with Microsoft. Even so, we do not speak in the name of Microsoft :-)

    >> Its just a loop and ms db engine will use multiple nested loops judging from MS SQL history.

    Where do you get your information from?!? What's your experience with SQL Server and what is your knowledge about what/how SQL Server really works behind the scenes, which backup your statements?

    I cannot say that I have a lot. I work with SQL Server from 2000, and I do work with one database which is over 100TB and several hundreds more databases. It is not a lot probably, but maybe I have something to contribute and just maybe it is good idea to listen. Who know, maybe... just maybe, I will say something smart from time to time :-)

    You speak about how SQL Server works, while there are people here who actually know how SQL Server works... 

    I teach about how SQL Server works behind the scenes and I lecture about SQL Server internals from time to time in events around the world. Just several hours ago I lectured at Microsoft Israel about SQL Server internals: https://www.meetup.com/Data-Platform-Meetup-Israel/events/262090664/ . In fact I have a lecture where I create new database -> create new table -> add rows to the table -> detach the database -> and I open the data file using Notepad++  and read the data. I do it live in events like this one: https://www.sqlsaturday.com/623/Sessions/Details.aspx?sid=63290

    I have other topics and lectures. For example at Microsoft Ignite I will lecture about new feature in SQL Server 2019 and Azure Database, but These (SQL Server internals) are topics which I like the most! 

    Do you really think that you should argue about how SQL Server works behind the scenes, or maybe this is a good chance to learn free...

    >> loop is so bad then why offer it as part of the language

    How many times did you hear that eating meat, salty food, or sweet foods is bad for your health?!? and yet a person cannot live without salt! There was time in history and places that the price of salt was like the price of gold.

    Loops are important and there are cases that loop is the only way to get to the result well. In other cases loop return better performance then other solutions. BUT!!! As a golden rule you should try to avoid using loops in tabular databases (not only in SQL Server!) since tabular databases are developed to provide best performance for working with SET of data and not with value data (which in some cases leads to using procedures or algorithms behind the scenes which does not work well with loops).

    >> There is a reason for everything think about it. 

    Who is "everything"?!? Do you speak in my name?

    There is reason why expert get good money and companies do not takes "everything" to do the work. "everything" is not something that I take as a base for a true. We should listen to "The Ones" and not the "everything" :-) 

    It is common for people to think like people and not like computers. It is common for people that do not understand the way a product is working under the scenes, to think about the way people solve something, while the application works totally different.

    >> The user posed a question and there was ONLY one string

    This is exactly the problem probably. Seems like you think about working with value instead of working with tables. Tabular Databases like SQL Server meant to work best with SET of data (tables) and not with a simple value. If you have a single value then you probably should work in client side in languages like Fortran, C, C++, C# and so on.

    The first assumption that we should (IMO) and we do when we are in SQL Server forum, is that the question is related to table of data and not a single value (unless explicitly told otherwise).

    >> Just because you say something does not mean that it is truth

    I agree, it is not true because Erland say so, but Erland say so because it is true.

     

    In any way, good luck in any solution you chose to use and good luck to your clients.


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]



    • Edited by pituachMVP Tuesday, October 8, 2019 1:33 PM
    Tuesday, October 8, 2019 9:41 AM
  • When I open a Oracle reference manual and I don’t find the word ANSI and not one single line of code mentioning foolish ‘inner join’ stuff and Microsoft manual is so full of it. Not one line of ANSI join code in the entire Oracle manual and that says a lot what Oracle thinks about ANSI.

    You work with SQL since 2000? Jee I finished my masters MS long before that and I was doing SQL when Windows just started out (so Windows did not even mature and Oracle was not there) and I was doing SQL in university. I knew it had future and everyone was saying something else. Even SQL did not exist. It was called EQUEL. 

    One issue about performance. Don't worry too much about it. If the query is correct and does what it is suppose to do then it should be ok. Performance is something extra. If something is done in 1 second but wrong answer and other solution takes several minutes but correct then performance is poor choice. Also, unless someone specifically asks for it (performance) then it should not be focus.  









    Tuesday, October 8, 2019 1:41 PM
  • When I open a Oracle reference manual and I don?t find the word ANSI and not one single line of code mentioning foolish ?inner join? stuff and Microsoft manual is so full of it. Not one line of ANSI join code in the entire Oracle manual and that says a lot what Oracle thinks about ANSI.

    If you look at https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SELECT.html#GUID-CFA006CA-6FF1-4972-821E-6996142A51C6__I2066611
    you will find several instances of RIGHT OUTER JOIN and LEFT OUTER JOIN and even FULL OUTER JOIN. The inner-join examples uses the comma syntax and not the INNER JOIN keywords, but guess what: the comma syntax is ANSI too!

    I was also surprised to see CROSS/OUTER APPLY - I was under the impression that this is a proprietary SQL Server operator, of which the name is LATERAL in the ANSI standard. LATERAL appears on the same page, by the way.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, October 8, 2019 9:30 PM
  • When I open a Oracle reference manual and I don?t find the word ANSI and not one single line of code mentioning foolish ?inner join? stuff and Microsoft manual is so full of it. Not one line of ANSI join code in the entire Oracle manual and that says a lot what Oracle thinks about ANSI.

    If you look at https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SELECT.html#GUID-CFA006CA-6FF1-4972-821E-6996142A51C6__I2066611
    you will find several instances of RIGHT OUTER JOIN and LEFT OUTER JOIN and even FULL OUTER JOIN. The inner-join examples uses the comma syntax and not the INNER JOIN keywords, but guess what: the comma syntax is ANSI too!

    I was also surprised to see CROSS/OUTER APPLY - I was under the impression that this is a proprietary SQL Server operator, of which the name is LATERAL in the ANSI standard. LATERAL appears on the same page, by the way.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    If you download the following language reference from Oracle corp (PL/SQL Language Reference Manual):

    https://docs.oracle.com/database/121/LNPLS/toc.htm

    Then do a global search for 'inner join' or 'outer join' the search come up nil. The reason your page showed 'outer join' was for completeness because the 'Select construct' indicated that it supported those type and has to include them but you will not find any other examples using ANSI join in the entire manual you chose.  

    Also, speaking about performance, it is becoming more hardware issue. Earlier maybe it was an issue (10 years back) but how memory and disk space has become cheap along with processor speed now and in future database query performance will become less of an issue related to query itself. 




    Wednesday, October 9, 2019 4:40 AM