need help - 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)

need help

Author

Topic

want_to_know
Starting Member

13 Posts

Posted - 2008-03-19 : 09:12:29

Hi,
can anyone help me on this simple problem?

i have a employee_id (varchar(25)). I want to find the maximum length of the characters in the employee_id.

I tried 'select max(len(employee_id)) from table' but it gave me 25 which is the total no of characters of the datatype that i assigned to the column.

For example

employee_id
------------
sdfhkd12
fdagkj2345
dfafsf234234
fdaf2435
dfsf

i want to display the max length of this values which should be 12.

Can anyone help me on this please? i would appreciate.

Thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-19 : 09:21:03

[code]Select max(id_len)
from
(
Select datalength(employee_id) as id_len
from table
) t[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-03-19 : 09:21:31

[code]
declare @table table (emp varchar(25))
insert into @table
select 'sdfhkd12' union all
select 'fdagkj2345' union all
select 'dfafsf234234' union all
select 'fdaf2435' union all
select 'dfsf' union all
select 'a'

select * from @table
select max(len(emp)) from @table
[/code]

really? my result from the above gives me 12. are you sure there isn't a longer value in your table? any whitespace?

Em

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-19 : 09:24:34

I got 12 when i ran your query.


DECLARE @T table
(
employee_id varchar(50)

)
INSERT @T
SELECT 'sdfhkd12'
UNION ALL
SELECT 'fdagkj2345'
UNION ALL
SELECT 'dfafsf234234'
UNION ALL
SELECT 'fdaf2435'
UNION ALL
SELECT 'dfsf'

select max(len(employee_Id)) from @T


output
---------------


(5 row(s) affected)

-----------
12

(1 row(s) affected)

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-03-19 : 09:32:34

try....
select max(len(ltrim(employee_id))) from yourTable

NB. you don't to do a rtrim() as well because len() ignores trailing blanks already

Em

want_to_know
Starting Member

13 Posts

Posted - 2008-03-19 : 10:03:45

Thanks. But It still gives me 25 (my data may have white spaces too)

Select max(id_len)
from
(
Select datalength(employee_id) as id_len
from table
) t[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
[/quote]

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-03-19 : 10:05:03

using datalength() will count the whitespace. did you try it with ltrim() ?

Em

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-19 : 10:05:15

quote:
Originally posted by want_to_know

Thanks. But It still gives me 25 (my data may have white spaces too)

Select max(id_len)
from
(
Select datalength(employee_id) as id_len
from table
) t[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

[/quote]

If you don't want to count whitespaces, use elancaster's solution using ltrim() and len() function.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

want_to_know
Starting Member

13 Posts

Posted - 2008-03-19 : 10:05:16

Thanks.
But there are 1 million rows in my table, my column may have leading or trailing blanks.
I still got 25.

Any more suggestions

quote:
Originally posted by elancaster

declare @table table (emp varchar(25))
insert into @table
select 'sdfhkd12' union all
select 'fdagkj2345' union all
select 'dfafsf234234' union all
select 'fdaf2435' union all
select 'dfsf' union all
select 'a'

select * from @table
select max(len(emp)) from @table

really? my result from the above gives me 12. are you sure there isn't a longer value in your table? any whitespace?

Em

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-03-19 : 10:06:54

or....
select max(len(replace(employee_id,' ',''))) from yourTable

...this would be better if there is embedded whitespace too

Em

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-03-19 : 10:14:36

You may have carriage returns and line feeds in the data as well, and they will need to be replaced. Was this data imported from a fixed-width flat file?

Jim

want_to_know
Starting Member

13 Posts

Posted - 2008-03-19 : 10:19:11

SELECT MAX(DATALENGTH(RTRIM(id))) AS Expr1
FROM table

SELECT MAX(DATALENGTH(LTRIM(id))) AS Expr1
FROM table

SELECT MAX(DATALENGTH(LTRIM(RTRIM(id))) AS Expr1
FROM table

SELECT MAX(DATALENGTH(RTRIM(LTRIM(id))) AS Expr1
FROM table

SELECT MAX(LEN(RTRIM(LTRIM(client_member_id)))) AS Expr1
FROM members
SELECT MAX(LEN(LTRIM(client_member_id))) AS Expr1
FROM members
SELECT MAX(LEN(RTRIM(client_member_id))) AS Expr1
FROM members
SELECT MAX(LEN(LTRIM(RTRIM(client_member_id)))) AS Expr1
FROM members

These all gave me 25 .... Can you guys help me out

Still gives me 25

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-19 : 10:22:28

I suspect then you have some non-printing character embedded in your data.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-03-19 : 10:26:50

did you try it with replace()? if it still gives you 25, then you have more than just whitespace in there too

Em

want_to_know
Starting Member

13 Posts

Posted - 2008-03-19 : 10:32:54

these files are from flat files ( not sure if fixed or comma separated)

can you give me advise for these files

Thanks

tm
Posting Yak Master

160 Posts

Posted - 2008-03-19 : 10:38:22

Maybe you should find out which employee_id has 25 chars in length first by ..

select employee_id from table where len(rtrim(employee_id)) = 25
or
select employee_id from table where len(ltrim(rtrim(employee_id))) = 25

This at least is a start before looking what characters that you might have hidden in what records.

want_to_know
Starting Member

13 Posts

Posted - 2008-03-19 : 11:13:44

Yes actually there were 14 records with 25 characters , i am sorry i am so stupid..

Thank you everyone.

God bless you all

quote:
Originally posted by tm

Maybe you should find out which employee_id has 25 chars in length first by ..

select employee_id from table where len(rtrim(employee_id)) = 25
or
select employee_id from table where len(ltrim(rtrim(employee_id))) = 25

This at least is a start before looking what characters that you might have hidden in what records.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-03-19 : 11:14:43

phew! at least you came back and told us eh?

want_to_know
Starting Member

13 Posts

Posted - 2008-03-19 : 11:22:30

Ha ha

but i still hav another question

shall i shoot or you guys want to quit?

quote:
Originally posted by elancaster

phew! at least you came back and told us eh?

Em

want_to_know
Starting Member

13 Posts

Posted - 2008-03-19 : 11:35:52

Now i see that the id has other characters as -,whitespace in between
My goal is to get the characters without the last two characters.
for example

id
--
abc12
hellohi01
takecare08
blah bla09
heh hehhu11
tata-tata99

i want abc,hellohi,takecare etc without the last two chars.

how can select this

I would appreciate it

Thanks

quote:
Originally posted by elancaster

phew! at least you came back and told us eh?

Em

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-03-19 : 14:10:51

take a look at this quick example and see if it helps...


declare @test varchar(30)
set @test = 'abc-test this12'

--getting rid of the '-' and ' ' 
select  replace(replace(@test,'-',''),' ','')

--getting rid of the last 2 characters
select left(@test,len(@test)-2)

--doing both...?
select left(replace(replace(@test,'-',''),' ',''),len(replace(replace(@test,'-',''),' ',''))-2)

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources