SQL - Convert SQL Column to Camel Case

Issue: Converting a sql column comprised of strings into camel casing format.

Solution: Create a camel casing function, run it against the column, and delete the function if you like afterwards.

 1)
CREATE FUNCTION [dbo].[CamelCase]
(@Str varchar(8000))
RETURNS varchar(8000) AS
BEGIN
  DECLARE @Result varchar(2000)
  SET @Str = LOWER(@Str) + ' '
  SET @Result = ''
  WHILE 1=1
  BEGIN
    IF PATINDEX('% %',@Str) = 0 BREAK
    SET @Result = @Result + UPPER(Left(@Str,1))+
    SubString  (@Str,2,CharIndex(' ',@Str)-1)
    SET @Str = SubString(@Str,
      CharIndex(' ',@Str)+1,Len(@Str))
  END
  SET @Result = Left(@Result,Len(@Result))
  RETURN @Result
END

2)
Update SomeTable Set SomeName = dbo.CamelCase(SomeName)

Copy SQL to Excel Missing Rows

Issue:

When you try to copy rows from SQL server and paste into Excel but experience missing rows either by right clicking and coping with headers or saving as a CSV.

Solution:

The problem is double quotes "

Excel will skip all rows in between the start and end of a double quotes.

1) Either trim off the quotes in SQL

or

2) Right click and save as text, tab delimited, from SQL and then open in Excel using the default workflow selections during import.

Connect to SQL Server from Matlab

Alternatively you can create and use an ODBC connection, but if you would rather not do that for every database and server you wish to connect to, you can do the following using the JDBC driver.

1. Download the JDBC driver from Microsoft from:

http://www.microsoft.com/downloads/details.aspx?FamilyID=99b21b65-e98f-4a61-b811-19912601fdc9&displaylang=en

2. Execute it, and it will be decompressed to the selected location

3. Create folder c:/SQLJDBC and place sqljdbc_2.0 inside it.

4. Start the Notepad application as administrator and open the file:

C:\Program Files\MATLAB\R2010a\toolbox\local\classpath.txt

5. Add a reference like this to the JDBC driver after the last line of that file:

c:/SQLJDBC/sqljdbc_2.0/enu/sqljdbc4.jar

6. After that you have to restart MATLAB and then you can test the connection:

dbConn = database('master', 'user', 'password', 'com.microsoft.sqlserver.jdbc.SQLServerDriver', 'jdbc:sqlserver://localhost:1433;databaseName=master;');

7. To test the connection you can execute:

ping(dbConn);

8. Please note that the local instance of SQL Server requires you to have the TCP/IP protocol network connections enabled.

----------------

Other references:   http://www.mathworks.com/help/toolbox/database/ug/database.html