none
Interview question on SQL Server

    Question

    1. How you will you load csv file with address field which is having multiple values in SQL.

    2.What is dynamic sql and if you want to print it before executing then how will you print it with formatting for readability.

    3.How will you separate the customer name from below file once it has be loaded into your staging tables

    Input

    @@#$$%ami$$%5689t78jh6789a

    @@#$$%so$$%5689t78han89a

    @@#vipin$$%5689t78jha6789

    @@#aru$$%5689t78jha6789a

    Output

    Amitjha

    Sohan

    Vipinjha

    Arunjha

    Thanks in advance

    regards,

    Vipin jha


    Thankx & regards, Vipin jha MCP



    Thursday, August 28, 2014 3:43 AM

Answers

  • 1. Not clear.

    Use the SSIS Import/Export Wizard: http://www.sqlusa.com/bestpractices/ssis-wizard/

    2. I include CHAR(13) carriage return characters in the assembly of the dynamic SQL statement for PRINT readability:

    http://www.sqlusa.com/bestpractices/dynamicsql/

    3. Use nested REPLACE-s to get rid of the unwanted characters:

    http://www.sqlusa.com/bestpractices2008/nestedreplace/

    or build a UDF:

    http://www.sqlusa.com/bestpractices2005/stringcleanser/


    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012









    Thursday, August 28, 2014 4:48 AM
    Moderator
  • For third question, please find the below link:

    http://gallery.technet.microsoft.com/Separate-AlphabetsNumeralsA-0d84db79

    A Read-made:

    DECLARE @Sample TABLE(string VARCHAR(100));
    
    INSERT INTO @Sample
    SELECT '@@#$$%ami$$%5689t78jh6789a' UNION ALL
    SELECT '@@#$$%so$$%5689t78han89a' UNION ALL
    SELECT '@@#vipin$$%5689t78jha6789' UNION ALL
    SELECT '@@#aru$$%5689t78jha6789a' 
    
    ;WITH Num1 (n) AS (SELECT 1 UNION ALL SELECT 1),
    Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
    Num3 (n) AS (SELECT 1 FROM Num2 AS X, Num2 AS Y),
    Num4 (n) AS (SELECT 1 FROM Num3 AS X, Num3 AS Y),
    Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num4),
    FinalOutput AS (
    SELECT string, 
    (SELECT CASE WHEN SUBSTRING(string, n, 1) NOT LIKE '[^0-9]'
               THEN SUBSTRING(string, n, 1)
              ELSE ''
          END + ''
    FROM Nums
    WHERE n <= LEN(string)
    FOR XML PATH('')) AS [Separate Only Numbers],
    (SELECT CASE WHEN SUBSTRING(string, n, 1) NOT LIKE '[^A-Za-z]'
               THEN SUBSTRING(string, n, 1)
              ELSE ''
          END + ''
    FROM Nums
    WHERE n <= LEN(string)
    FOR XML PATH('')) AS [Separate Only Alphabets],
    (SELECT CASE WHEN SUBSTRING(string, n, 1) NOT LIKE '[^A-Za-z0-9]'
               THEN SUBSTRING(string, n, 1)
              ELSE ''
          END + ''
    FROM Nums
    WHERE n <= LEN(string)
    FOR XML PATH('')) AS [Separate Only AlphaNumerals]
    FROM @Sample)
    SELECT * FROM FinalOutput;

    Thursday, August 28, 2014 5:00 AM
    Answerer
  • You can use CLR or a user defined function.

    Example of udf:

    CREATE FUNCTION ufn_GetName(@MYSTR VARCHAR(MAX))
    RETURNS VARCHAR(MAX)
    AS
    BEGIN
     DECLARE @TempStr AS VARCHAR(MAX) = ''
     DECLARE @CURPOS AS INT = 0
     WHILE @CURPOS <= LEN(@MYSTR) BEGIN
     IF UPPER(SUBSTRING(@MYSTR, @CURPOS,1)) BETWEEN 'A' AND 'Z' BEGIN
      IF LEN(@TempStr) = 0 BEGIN
       SET @TempStr = @TempStr + UPPER(SUBSTRING(@MYSTR, @CURPOS,1))
      END ELSE BEGIN
      SET @TempStr = @TempStr + LOWER(SUBSTRING(@MYSTR, @CURPOS,1))
      END
     END
     SET @CURPOS = @CURPOS + 1
     END
     RETURN @TempStr
    END

    You can call it like this:

    select dbo.ufn_GetName('@@#$$%ami$$%5689t78jh6789a')


    Thursday, August 28, 2014 5:43 AM
  • Hi Vipin,

    About the first question, we need to verify if you want to import the data from CSV file into SQL Server. If so, there are several methods to complete it. As Kalman’s post, you can use SSIS or Import/Export Wizard in SQL Server. Also you can use bulk import options such as BULK INSERT and  BCP to import a CSV file into SQL Server.

    The T-SQL BULK INSERT command is of the easiest ways to import CSV file into SQL Server. There is an example as follows. Refer to this blog: Import Data from a Text or CSV file into SQL Server.

    Source data in the CSV file looks something like this:

    HOU009,Windows Phone 7,Will Martinez,
    11/10/2011,Houston; TX,999,2
    HOU010,WPF for Business Applications,Will Martinez,

    In SQL Server, import data from the CSV file using the below statement:

    CREATE Table #csvtest(
       [code] [varchar](50) NOT NULL,
       [description] [varchar](200) NULL,
       [instructor] [varchar](50) NULL,
       [date] [date] NULL,
       [venue] [varchar](50) NULL,
       [price] [money] NULL,
       [duration] [int] NULL)
       
    bulk insert #csvtest
    from 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\csvtest.csv'
    with (fieldterminator = ',', rowterminator = '\n')
    go
    
    select *from #csvtest
    --below is result



    About your second and third questions, you can review others' posts.

    Thanks,
    Lydia Zhang



    Friday, August 29, 2014 3:30 AM
    Moderator

All replies

  • 1. Not clear.

    Use the SSIS Import/Export Wizard: http://www.sqlusa.com/bestpractices/ssis-wizard/

    2. I include CHAR(13) carriage return characters in the assembly of the dynamic SQL statement for PRINT readability:

    http://www.sqlusa.com/bestpractices/dynamicsql/

    3. Use nested REPLACE-s to get rid of the unwanted characters:

    http://www.sqlusa.com/bestpractices2008/nestedreplace/

    or build a UDF:

    http://www.sqlusa.com/bestpractices2005/stringcleanser/


    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012









    Thursday, August 28, 2014 4:48 AM
    Moderator
  • For third question, please find the below link:

    http://gallery.technet.microsoft.com/Separate-AlphabetsNumeralsA-0d84db79

    A Read-made:

    DECLARE @Sample TABLE(string VARCHAR(100));
    
    INSERT INTO @Sample
    SELECT '@@#$$%ami$$%5689t78jh6789a' UNION ALL
    SELECT '@@#$$%so$$%5689t78han89a' UNION ALL
    SELECT '@@#vipin$$%5689t78jha6789' UNION ALL
    SELECT '@@#aru$$%5689t78jha6789a' 
    
    ;WITH Num1 (n) AS (SELECT 1 UNION ALL SELECT 1),
    Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
    Num3 (n) AS (SELECT 1 FROM Num2 AS X, Num2 AS Y),
    Num4 (n) AS (SELECT 1 FROM Num3 AS X, Num3 AS Y),
    Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num4),
    FinalOutput AS (
    SELECT string, 
    (SELECT CASE WHEN SUBSTRING(string, n, 1) NOT LIKE '[^0-9]'
               THEN SUBSTRING(string, n, 1)
              ELSE ''
          END + ''
    FROM Nums
    WHERE n <= LEN(string)
    FOR XML PATH('')) AS [Separate Only Numbers],
    (SELECT CASE WHEN SUBSTRING(string, n, 1) NOT LIKE '[^A-Za-z]'
               THEN SUBSTRING(string, n, 1)
              ELSE ''
          END + ''
    FROM Nums
    WHERE n <= LEN(string)
    FOR XML PATH('')) AS [Separate Only Alphabets],
    (SELECT CASE WHEN SUBSTRING(string, n, 1) NOT LIKE '[^A-Za-z0-9]'
               THEN SUBSTRING(string, n, 1)
              ELSE ''
          END + ''
    FROM Nums
    WHERE n <= LEN(string)
    FOR XML PATH('')) AS [Separate Only AlphaNumerals]
    FROM @Sample)
    SELECT * FROM FinalOutput;

    Thursday, August 28, 2014 5:00 AM
    Answerer
  • You can use CLR or a user defined function.

    Example of udf:

    CREATE FUNCTION ufn_GetName(@MYSTR VARCHAR(MAX))
    RETURNS VARCHAR(MAX)
    AS
    BEGIN
     DECLARE @TempStr AS VARCHAR(MAX) = ''
     DECLARE @CURPOS AS INT = 0
     WHILE @CURPOS <= LEN(@MYSTR) BEGIN
     IF UPPER(SUBSTRING(@MYSTR, @CURPOS,1)) BETWEEN 'A' AND 'Z' BEGIN
      IF LEN(@TempStr) = 0 BEGIN
       SET @TempStr = @TempStr + UPPER(SUBSTRING(@MYSTR, @CURPOS,1))
      END ELSE BEGIN
      SET @TempStr = @TempStr + LOWER(SUBSTRING(@MYSTR, @CURPOS,1))
      END
     END
     SET @CURPOS = @CURPOS + 1
     END
     RETURN @TempStr
    END

    You can call it like this:

    select dbo.ufn_GetName('@@#$$%ami$$%5689t78jh6789a')


    Thursday, August 28, 2014 5:43 AM
  • Hi Vipin,

    About the first question, we need to verify if you want to import the data from CSV file into SQL Server. If so, there are several methods to complete it. As Kalman’s post, you can use SSIS or Import/Export Wizard in SQL Server. Also you can use bulk import options such as BULK INSERT and  BCP to import a CSV file into SQL Server.

    The T-SQL BULK INSERT command is of the easiest ways to import CSV file into SQL Server. There is an example as follows. Refer to this blog: Import Data from a Text or CSV file into SQL Server.

    Source data in the CSV file looks something like this:

    HOU009,Windows Phone 7,Will Martinez,
    11/10/2011,Houston; TX,999,2
    HOU010,WPF for Business Applications,Will Martinez,

    In SQL Server, import data from the CSV file using the below statement:

    CREATE Table #csvtest(
       [code] [varchar](50) NOT NULL,
       [description] [varchar](200) NULL,
       [instructor] [varchar](50) NULL,
       [date] [date] NULL,
       [venue] [varchar](50) NULL,
       [price] [money] NULL,
       [duration] [int] NULL)
       
    bulk insert #csvtest
    from 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\csvtest.csv'
    with (fieldterminator = ',', rowterminator = '\n')
    go
    
    select *from #csvtest
    --below is result



    About your second and third questions, you can review others' posts.

    Thanks,
    Lydia Zhang



    Friday, August 29, 2014 3:30 AM
    Moderator