Trimming - 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

Trimming

Author

Topic

phong919
Starting Member

29 Posts

Posted - 2008-03-31 : 10:12:44

Hello all, i'm new to using sql server. i was wondering if i anyone can assist me with the following:

M5 - AJJJJJJJ

How can i just get the '5' in another column? Thank you.

nr
SQLTeam MVY

12543 Posts

Posted - 2008-03-31 : 10:14:01

select fld = substring(fld,2,1)

Suspect that's not really your question though.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-03-31 : 10:14:15

is it always the 2nd character in the string? give us some more sample data

Em

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-03-31 : 10:34:04

This will give you the first number in the string

declare @q varchar(20)
SET @q ='M5 - AJJJ5JJJ'

SELECT SUBSTRING(@q,PATINDEX( '%[0-9]%',@q),1)

Jim

phong919
Starting Member

29 Posts

Posted - 2008-03-31 : 10:36:40

thank you for the quick response.

there's one column in the table that contains

M5 - kdfjskdfjsdkfjsdk

So i created a view to select only the columns i want and i need the '5' for another column for the view. i hope that explains more about what i need. thanks.

GhantaBro
Posting Yak Master

215 Posts

Posted - 2008-03-31 : 11:48:16

quote:
Originally posted by nr

select fld = substring(fld,2,1)

Suspect that's not really your question though.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

if it is always the second character then this works.. select that in differend field instead. I have the same question... is that what you are looking for? 2nd character from the string to another column?

phong919
Starting Member

29 Posts

Posted - 2008-03-31 : 12:07:20

yeah that is what i'm looking for, now say if the following happens:

M5 - ididididiid
M5.1 - ieieijgjgjgj
M5.2 - eieiieie

All i need with the first new column is '5' but i also need a new second column with 'M5','M5.1','M5.2'. Is there some conditional statement that can be added?

Thank you again.

GhantaBro
Posting Yak Master

215 Posts

Posted - 2008-03-31 : 12:53:20

yeah use substring, charindex and patindex... u should do it..

look for the substring that starts from 2nd char and goes before '-' or space whatever u have consistently.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-03-31 : 13:07:38

Just change what you're looking for, this gives everything before the first blank

Jim

declare @q varchar(20)
SET @q ='M5.1 - AJJJ5JJJ'

SELECT SUBSTRING(@q,1,CHARINDEX( ' ',@q) )

phong919
Starting Member

29 Posts

Posted - 2008-03-31 : 14:19:31

i'm sorry but i'm not sure on how to do that. Can you put an example for me? thank you.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-03-31 : 14:31:06

I thought I had given examples. Read this link as an example of the data you need to provide in order to get an answer.

Jim

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74221

phong919
Starting Member

29 Posts

Posted - 2008-03-31 : 14:58:45

Thanks.

CASE WHEN isnumeric(substring(sTask , 2 , 1)) = 1 AND isnumeric(substring(sTask , 3 , 1)) = 1 THEN CONVERT (integer , substring(sTask , 2 , 2)) WHEN isnumeric(substring(sTask , 2 , 1)) = 1 THEN CONVERT (integer , substring(sTask , 2 , 1)) END

the following conditional formatting will fail if the 3 character is a string. Is there a way around it?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-03-31 : 15:21:27

And read this

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

phong919
Starting Member

29 Posts

Posted - 2008-04-01 : 00:11:40

Thanks for the post.

Can somone explain to me in the previous post with the CASE statement what the whole thing is about? I didn't write the logic, so i'm just trying to understand what it's doing. Thank you.

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-01 : 05:40:34

Well for one thing it's probably wrong.
It's assuming that if isnumeric returns 1 then the value can be convetred to an integer which is incorrect.
It's trying to say that if the second and third characters are integers then return them
otherwise if the the second is an integer then return that otherwise return null. The result is an integer.

It probably should be
select case when substring(sTask , 2 , 2) not like '%[^0-9]%' then CONVERT (integer , substring(sTask , 2 , 2))
when substring(sTask , 2 , 1) like '%[0-9]%' then CONVERT (integer , substring(sTask , 2 , 1)) end

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

phong919
Starting Member

29 Posts

Posted - 2008-04-01 : 10:17:53

OMG, it worked. Thank you so much nr. i was going crazy trying figure it out. Greatly appreciated.

phong919
Starting Member

29 Posts

Posted - 2008-04-01 : 10:19:02

Hey nr - would this also be wrong?

CASE WHEN isnumeric(substring(sTask , 2 , 1)) = 1 AND isnumeric(substring(sTask , 3 , 1)) = 1 THEN substring(sTask , 1 , 3) WHEN isnumeric(substring(sTask , 2 , 1)) = 1 THEN substring(sTask , 1 , 2) END

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-01 : 10:31:51

Depends on what you are trying to do.
For sTask = 'a.3tyuti'
that would return 'a.3'
for 'a.rtyrur'
it would return 'a.'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

phong919
Starting Member

29 Posts

Posted - 2008-04-16 : 12:24:11

Hey nr,

Anyone else will also be greatly appreciated.

Can you please assist with the following?

CASE WHEN isnumeric(substring(Task_Name , 2 , 1)) = 1 AND isnumeric(substring(Task_name , 3 , 1)) = 1 THEN substring(Task_name , 1 , 5) WHEN isnumeric(substring(Task_name , 2 , 1)) = 1 THEN substring(Task_name , 1 , 2) END

This case works fine but when it doesn't work when the result are the following:

i.e. M6.1 - balhdhhdhdhdhhdhdh = Results = M6.1 - Correct
i.e. M10.1 - hdhdhhdhdhdhdhd = Results = M10. - incorrect

How can i get M10.1 as my result and M6.1 without any errors. Is the case statement incorrect? thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-16 : 12:39:55

Your posted result doesnt look correct how did you get M10. in second case?Can you post your fullquery?

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-16 : 12:57:37

This looks like an issue with the problem definition.
Looks like you want a letter followed by a decimal number terminated by a space.
Nothing that has fixed values will work for this - you will have to at least find the location of the space.
patindex('%[^0-9%]',@str) gives the position of the first non numeric char.
If you can guarantee a space then
charindex(' ',@str,2) will start the search from the 2nd char.
so for your two strings
select left(@str,charindex(' ',@str,2))

To look for the characters before the first numeric char
declare @str varchar(1000)
select @str = 'M6.1 - balhdhhdhdhdhhdhdh'
select left(@str,patindex('%[0-9]%',@str)-1)

To use the same value as a derived table
select left(@str,charlen)
from (select charlen = patindex('%[0-9]%',@str)-1) a

We can now get the following numeric characters
declare @str varchar(1000)
select @str = 'M6.1 - balhdhhdhdhdhhdhdh'
select left(@str,charlen+numlen)
from
(select strlen, charlen, numlen = patindex('%[^0-9.]%',right(@str,strlen-charlen-1))
from (select strlen = len(@str), charlen = patindex('%[0-9]%',@str)-1) a
) b

this should work for anything of the form
xxxnnn.nnnxxxxxx = xxxnnn.nnn
xxxnnnxxxxx = xxxnnn

for
xxxnnn.nnn.nnnxxx = xxxnnn.nnn.nnn
xxxnnn.xxx = xxxnnn.

which you might need to cater for.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources