Category Archives: SQL Bugs

Searching the strings with leading spaces and trailing spaces in the sql server


Hi Folks,

We have issues in the production at our end where the master table is update with leading spaces and it is causing the incorrect results as it master table and configuration is present in those tables.

I quickly checked the leading spaces in the table using the below query

We have created sample tables for this demo

use tempdb

go

create table #tempo

(

id int,

name varchar(100)

)

insert #tempo

select 1,’chaitu’

union all

select 2,’pradee’

union all

select ‘3’,’ krishna’

select * from #tempo

Below is the query for getting the leading spaces in the name column

select * from #tempo where LEFT(name,1)=”

For trailing spaces

use tempdb

go

create table #tempo

(

id int,

name varchar(100)

)

insert #tempo

select 1,’chaitu’

union all

select 2,’pradee’

union all

select ‘3’,’krishna ‘ à trailing space is inserted

select * from #tempo

Below is the query for getting the tralining spaces in the name column

select * from #tempo where right(name,1)=”

Please let us know if you have other options

Thanks for viewing.

Regards,

Chaitanya,

https://mssqlbuzz.wordpress.com/

Advertisements

Unicode characters issue in sql server


Yesterday when we got an issue such that Greek characters are going to customer with? Characters

ex:

String: “how are you  ”

Greek language is Γεια σου τι κάνεις

We have checked all the Procedures and found the issue that the one procedure is taking this greek letters are storing the data in the table variable like below

declare @tablevariable table

(

id int primary key  identity(1,1),

stringcolumn varchar(100)

)

 

insert @tablevariable values (N’Γεια σου τι κάνεις’)

 

select * from @tablevariable

 

Result:

 

Ge?a s?? t? ???e??

 

So we identified the issue and this is due to the stringcolumn data type and stringcolumn is using the data type varchar and it is the ASCII standard one and it is not supporting the UNICODE  language

Nvarchar  is the Unicode supported datatype

 

Below are the Unicode character data type

 

nchar

 

Fixed-length Unicode data with a maximum length of 4,000 characters.

 

nvarchar

 

Variable-length Unicode data with a maximum length of 4,000 characters.

 

ntext

 

Variable-length Unicode data with a maximum length of 2^30 – 1 (1,073,741,823) characters.

Ex:

declare @tablevariable table

(

id int primary key  identity(1,1),

stringcolumn nvarchar(max)

)

 

insert @tablevariable values (N’Γεια σου τι κάνεις’)

 

select * from @tablevariable

 

Result:

Γεια σου τι κάνεις

 

Please use the N before the string so that it will be identified as Unicode

Please let us know if you have any concerns on this.

 

Thanks for viewing

%d bloggers like this: