LTrim or Trim a table column down.

[jojo79]

I have a table called tbl_pul, I import 3000 records weekly. 1 of the columns has data that looks like this "123456789/12345". I am trying to add another column that show just to the left of the "/", so it would add a column with just "123456789". Any ideas, I think I use the LTRIM or just regular Trim???


Thanks for all help

 

[SQLDenis]

you would use left and charindex

example

declare @v varchar(50)
select @v = '123456789/12345'

select left(@v,charindex('/',@v)-1)

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[SQLDenis]

you have to do some more work, so here is a complete example

[COLOR=blue]create[/color] [COLOR=blue]table[/color] blah (v [COLOR=blue]varchar[/color](50))
[COLOR=blue]insert[/color] blah [COLOR=blue]values[/color]([COLOR=red]'123456789/12345'[/color])
[COLOR=blue]insert[/color] blah [COLOR=blue]values[/color]([COLOR=red]'12345345'[/color])
[COLOR=blue]insert[/color] blah [COLOR=blue]values[/color]([COLOR=red]'123456789/33'[/color])
[COLOR=blue]insert[/color] blah [COLOR=blue]values[/color]([COLOR=red]'12345/2345'[/color])


[COLOR=blue]select[/color] [COLOR=blue]case[/color] [COLOR=blue]when[/color] len(v)-len([COLOR=#FF00FF]replace[/color](v,[COLOR=red]'/'[/color],[COLOR=red]''[/color])) = 0 [COLOR=blue]then[/color] v [COLOR=blue]else[/color] [COLOR=#FF00FF]left[/color](v,[COLOR=#FF00FF]charindex[/color]([COLOR=red]'/'[/color],v)-1)
[COLOR=blue]end[/color] v
[COLOR=blue]from[/color] blah

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions