August 2, 2016 at 4:18 am
Hello,
I have a problem, ltrim is not working. Here is the scenario
I imported data to a table from Excel.
When i select the data of a column(nvarchar) from the table the values are shown like
-USA
-UK with a prefixed "-"
I tried using ltrim on the column and the result is same. Can somebody please help me to resolve this.
Thanks in advance
Sanjay,
August 2, 2016 at 4:25 am
LTRIM only removes spaces. Use REPLACE to replace each "-" with a blank (""). Be careful if you have any non-leading "-"s that you want to keep, though.
John
August 2, 2016 at 4:27 am
Well 1st you need to understand What Ltrim does. Check the following link for detail.
In Short Following is the definition of these function
RTRIM and LTRIM simply remove spaces from a string on the right and left.
quick suggestion, You can do the following:
SELECT
x.CountryShortname, Replace(CountryShortname,'-','')
FROM
(
VALUES ('-USA'), ('-UK'), ('FRA-')
) x(CountryShortname)
hope it helps
August 2, 2016 at 6:51 am
John Mitchell-245523 (8/2/2016)
...Be careful if you have any non-leading "-"s that you want to keep, though.
SUBSTRING([column],PATINDEX('%[^-]%',[column]),255)
Far away is close at hand in the images of elsewhere.
Anon.
August 2, 2016 at 7:48 am
A few options. . .
SELECT N AS [ORIGINAL],
LTRIM([N]) AS [LTRIM - DOES NOTHING],
REPLACE([N], '-', '') AS [REPLACE - NOT QUITE],
STUFF([N], 1, CASE WHEN CHARINDEX('-', [N]) = 1 THEN 1
ELSE 0
END, '') AS [STUFF],
SUBSTRING([N], PATINDEX('%[^-]%', [N]), 255) AS [SUBSTRING]
FROM ( VALUES ( '-USA'), ( '-UK'), ( 'FR'), ( 'FR-CA') ) a ( N );
ORIGINAL LTRIM - DOES NOTHING REPLACE - NOT QUITE STUFF SUBSTRING
-------- -------------------- -------------------- --------------- ---------
-USA -USA USA USA USA
-UK -UK UK UK UK
FR FR FR FR FR
FR-CA FR-CA FRCA FR-CA FR-CA
As you're doing string manipulation, might be worth knocking a few options through a few million rows for a performance check.
August 2, 2016 at 11:40 am
I'd use CASE and STUFF to replace a leading '-' when one exists.
declare @input table (ID int identity(1,1) primary key, Country varchar(20));
insert into @input
values ('-UK'),('-US'),('SW'), ('DE'),('FR-CA')
select ID, CASE WHEN Country like '-%'
THEN STUFF(Country,1,1,'')
ELSE Country
END as Country
from @input
Slightly different format from Cadavre's but same principle.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply