JDev

Suppose values in your fields contain a period, and you want to return all characters that come before it. This is how you do in SQL Server:

select substring(column1, 1, charindex(’.', column1)-1 )
from myTable

WHERE datalength(ColumnName)> 0  shows non-empty fields

If you have single quotes in your text values, and you need to insert them in a SQL Server database the recommended approach is to use parameterized stored procedures. They will handle single quotes seamlessly.

However, there may be cases when you may need to construct an insert statement without resorting to parameters. In such cases, to handle text that contains single quotes you may have to use the Transact-SQL Replace function:

REPLACE ( string_expression , string_pattern , string_replacement )

An example that escapes single quotes by adding another single quote before it:

Declare @temp as varchar(64)
Set  @temp= (select …  from …)
Set @temp = Replace(@temp, ‘”’,””'’)

In other words, you need 4 single quotes as a second argument, and 6 single quotes as a second argument to escape single quotes.

The ‘desc’ command to get a list of table fields that is common in Oracle and MySql will fail in SQL Server. It turns out it is not sql-standard compliant. Instead, SQL Server uses the following command:

sp_columns myTableName

This will display detailed information about table structure.

If you need just individual columns, use the following command:

select column_name, data_type, character_maximum_length, *  
from information_schema.columns
where table_name = ‘myTableName’

order by ordinal_position