anything left of a character? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

anything left of a character?

Author

Topic

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-04-18 : 01:18:53

if i had many variables like 12-232
and 232131-232

and wanted only that left of the - symbol from that variable how could i do that?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-18 : 01:28:00

select col1, left(col1, charindex('-', col1) - 1)
from table1

Peter Larsson
Helsingborg, Sweden

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-04-18 : 01:30:59

worked thanx

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-04-18 : 02:03:20

what if i had

12123a

and

324242c

and wanted anything left of a non numeric character, and maybe also in another situation how would i store the character that apears

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-04-18 : 04:31:35

It is a immediate solution....check it is feasible for u...put the characters/special characters in table and do the comparison

Declare @tt table (char varchar(1))

insert @tt
select 'a' union
select 'b' union
select 'c' union
select 'd' union
select 'e' union
select 'f' union
select 'g' union
select 'h' union
select 'i' union
select 'j' union
select 'k' union
select 'l' union
select 'm' union
select 'n' union
select 'o' union
select 'p' union
select 'q' union
select 'r' union
select 's' union
select 't' union
select 'u' union
select 'v' union
select 'w' union
select 'x' union
select 'y' union
select 'z' union
select '-'

Declare @aa table (id varchar(10))

insert @aa
select '1234a841' union
select '872134u' union
select '4789' union
select '13T678' union
select '8902-688'

select id, col1 from
(
Select distinct id, (case
when charindex(char, lower(id)) > 0 then Left(id, (charindex(char, lower(id)) -1))
when IsNumeric(id)= 1 then id end) as col1
from @aa, @tt ) as b where b.col1 is not null

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-18 : 04:34:51

This is easier

-- prepare sample data
Declare @aa table (id varchar(10))

insert @aa 
select '1234a841' union
select '872134u' union
select '4789' union
select '13T678' union
select '8902-688'

-- show the expected output
select	id,
	case when pos = 0 then id else left(id, pos - 1) end as [newid]
from	(
		select	id,
			patindex('%[^0-9]%', id) as pos
		from	@aa
	) as x

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources