substring function - 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

SQL Server 2005 Forums

Transact-SQL (2005)

substring function

Author

Topic

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-06-20 : 03:26:45

it's early in the morning (well it is here anyway

) and i need a shot of logic...

in the data base i'm currently working with i have a varchar field that holds either a number or a collection of numbers in square brackets. I need to extract the number or the first number in square brackets if it's a list. i know it's gonna be a simple one but my head just won't do it?? i'm trying with substring and charindex to determine the position of the '[' but just not getting it this morning

quick sample of what the data in this column may look like...


declare @t table (col varchar(30))
insert into @t
select '2' union all
select '[5] [4]' union all
select ' [12] [1]'

so i need to get...


col
-------
2
5
12

EDIT-
OK, so i get this to work but only if there is actually square brackets


declare @t table (col varchar(30))
insert into @t
--select '2'			union all
select '[5] [4]'	union all
select ' [12] [1]'

select	 col
	,substring(col, charindex('[',col)+1, charindex(']',col)-charindex('[',col)-1)
from @t

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-20 : 03:49:11

SELECT left(REPLACE(REPLACE(col,'[',''),']',''),CASE WHEN CHARIDEX(' ',REPLACE(REPLACE(col,'[',''),']',''))>0 THEN CHARIDEX(' ',REPLACE(REPLACE(col,'[',''),']',''))-1 ELSE LEN(REPLACE(REPLACE(col,'[',''),']','')) END) FROM @t

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-06-20 : 03:54:05

actually that's not working

the result i get is...

2
5
... (blank)

Em

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-06-20 : 03:55:00

just to make it really scary... i need to use this 'substring' as part of a join to another table! great design eh? lol

Em

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-06-20 : 04:19:03

OK... got this, thoug if anyone has a slicker solution pleae let me know


declare @t table (col varchar(30))
insert into @t
select '2'	 union all
select '[5] [4]' union all
select ' [12] [1]'

select	 col
	,case when col like '%[[]%' then substring(col, charindex('[',col)+1, charindex(']',col)-charindex('[',col)-1) else col end
from @t

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-20 : 04:28:50

quote:
Originally posted by elancaster

just to make it really scary... i need to use this 'substring' as part of a join to another table! great design eh? lol

Em

add a ltrim also


SELECT left(ltrim(REPLACE(REPLACE(col,'[',''),']','')),CASE WHEN CHARINDEX(' ',ltrim(REPLACE(REPLACE(col,'[',''),']','')))>0 THEN CHARINDEX(' ',ltrim(REPLACE(REPLACE(col,'[',''),']','')))-1 ELSE LEN(ltrim(REPLACE(REPLACE(col,'[',''),']',''))) END) FROM @t

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-06-20 : 04:30:15

do you that is slicker than the solution i posted before?

Em

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-20 : 14:36:34

quote:
Originally posted by elancaster

do you that is slicker than the solution i posted before?

Em

Nope not slicker. In fact i was replying to the earlier post to rectify the problem in the then posted code.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-20 : 18:34:44

quote:

Originally posted by elancaster

OK... got this, thoug if anyone has a slicker solution pleae let me know


declare @t table (col varchar(30))
insert into @t
select '2'	 union all
select '[5] [4]' union all
select ' [12] [1]'

select	 col
	,case when col like '%[[]%' then substring(col, charindex('[',col)+1, charindex(']',col)-charindex('[',col)-1) else col end
from @t

Another method if you dont have more than two parts of numbers


select coalesce(parsename(col,2),parsename(col,1)) from
(
	select ltrim(replace(replace(replace(col,']',''),'[',''),' ','.')) as col from @t
) as t

Madhivanan

Failing to plan is Planning to fail

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-06-23 : 03:01:20

Thanks Madhi, though unfortunately i have no brief as to how many numbers could stored in this column

Em

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-23 : 03:40:23

[code]DECLARE @t TABLE (col varchar(30))
INSERT INTO @t
SELECT '2' UNION ALL
SELECT '[5] [4]' UNION ALL
SELECT ' [12] [1]'

SELECT col
, CASE WHEN CHARINDEX(']', col) > 0
THEN LTRIM(REPLACE(left(col, CHARINDEX(']', col) - 1), '[', ''))
ELSE col
END
FROM @t[/code]

KH
[spoiler]Time is always against us[/spoiler]

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-06-23 : 03:47:59

now that i like

cheers man

Em

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-23 : 03:50:25

[code]SELECT REPLACE(REPLACE(REPLACE(LEFT(Col, CHARINDEX(']', Col + ']')), ' ', ''), ']', ''), '[', '')
FROM @t[/code]

E 12°55'05.25"
N 56°04'39.16"

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-06-23 : 03:57:10

cool! no case

...and on a monday morning too

Em

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-23 : 04:02:09

Can there be data in Col column that look like this?

declare @t table (col varchar(30))
insert into @t
select '8' union all
select '2 9' union all
select '[5] [4]' union all
select '[5] 4' union all
select '5 [4]' union all
select ' [12] [1]'

Multiple values without brackets?
Multiple values with mixed brackets and space delimiter?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-23 : 04:16:24

[code]declare @t table (col varchar(30))
insert into @t
select '555' union all
select '8 ' union all
select ' 2 9' union all
select '[798][123]' union all
select '[101] 66' union all
select '17 [333]' union all
select ' [12] [1]'

SELECT Col,
REPLACE(LEFT(LTRIM(Col), CHARINDEX(']', REPLACE(LTRIM(Col), ' ', ']') + ']') - 1), '[', '')
FROM @t[/code]

E 12°55'05.25"
N 56°04'39.16"

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-06-23 : 04:21:48

i'm told that shouldn't be an issue, though it's nice to know i can handle it when the exception to the rule suddenly crops up 6 months down the line

thanks man

Em

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-23 : 04:27:49

Better safe than sorry

And look! Still no CASE.

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-23 : 09:03:07

quote:

Originally posted by khtan

DECLARE @t TABLE (col varchar(30))
INSERT INTO @t
SELECT '2'	 UNION ALL
SELECT '[5] [4]' UNION ALL
SELECT ' [12] [1]' 

SELECT	  col
	, CASE 	WHEN CHARINDEX(']', col) > 0 
			THEN LTRIM(REPLACE(left(col, CHARINDEX(']', col) - 1), '[', '')) 
			ELSE col 
			END
FROM 	@t

KH
[spoiler]Time is always against us[/spoiler]

Tan, nice to see you back
Where were you for long time?

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-23 : 09:41:40

quote:
Originally posted by madhivanan

Tan, nice to see you back
Where were you for long time?

Madhivanan

Failing to plan is Planning to fail

Kind of busy with work & projects. Actually still in the mist of a project. Will be quite busy until end of the year.

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-23 : 09:43:26

quote:
Originally posted by khtan

quote:
Originally posted by madhivanan

Tan, nice to see you back
Where were you for long time?

Madhivanan

Failing to plan is Planning to fail

Kind of busy with work & projects. Actually still in the mist of a project. Will be quite busy until end of the year.

KH
[spoiler]Time is always against us[/spoiler]

I thought you were very frequently swapping the countries

Madhivanan

Failing to plan is Planning to fail

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources