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,
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
SQL BLOGGING 