Solved

LTrim in SQL Server not working

Posted on 2009-05-15
10
822 Views
Last Modified: 2012-05-07
Hello experts,
I have a table in which for one of the colums there is a space and becos of that all the records that have space are appearing on the top in my dropdownlist

In spite of using LTrim(RTrim(Name)
or just LTrim
my problem is not solved please help

Select ID, Name from EmployerGroups order by Name

Open in new window

0
Comment
Question by:rowmark
  • 5
  • 5
10 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24398857
did u update the records ?

UPDATE EmployerGroups
SET Name = LTrim(RTrim(Name)
0
 

Author Comment

by:rowmark
ID: 24398877
Thanks aneesh.. I did update all the records even then some are not fixed.. How can I fix this issue
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24398950
> some are not fixed
can u provide some sample data
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:rowmark
ID: 24399005
Below are the records:
if you look at the first 4 records there is a space
Thank

19C00C68-FE9D-43BA-B0F3-023A19DC74AA      
E9776B45-AE35-459E-ABEF-D101D13A3A2F      
52C95EF6-862C-4352-801E-994437ABC5FD       David Andrews
FC5AECE4-E922-483D-BC4D-9AC88364EC95       Equities
58EA0141-D34C-413C-B98E-C19E32641DB3       NewOne
B3B3E3EF-E091-491D-AA08-D64DEE0D4681       Samuel Adams
5786800F-E500-49D1-837B-9399817E7DDA      AAA MidAtlantic
F92E56D4-0998-4505-A98B-B2C3CEA17009      Abe Smith
11790D20-6009-43C0-9FA4-CAB7AA781406      ADP
8FFEFE80-7AC6-45BA-8634-835AD2092106      Alan Steinthal
90A82FC4-80F9-416A-BCC0-6F453914732D      Alex Betinski
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24399031
SELECT * FROM EmployerGroups
WHERE LEN(Name) <>  LEN(LTrim(RTrim(Name))
0
 

Author Comment

by:rowmark
ID: 24399101
The above query is returning 0 records..
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24399124
i think it must be some other charecters, it is not the space
SELECT ASCII(Name) FROM EmployerGroups  WHERE <PrimaryKEy of those 4 records >
0
 

Author Comment

by:rowmark
ID: 24399146
NULL
NULL
160
160
160
160
160
65
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 24399176

UPDATE EmployerGroups
SET Name = REPLACE (NAME, char(160) ,'')
WHERE NAME LIKE char(160)+'%'
0
 

Author Closing Comment

by:rowmark
ID: 31582075
Perfect. Thanks
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server 2012 r2 - Sum totals 2 28
Sql query with where clause 2 33
get_systemdrive info from tsql? 1 18
TSQL Challenge... 7 35
I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question