How to read first few chareters from table

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)

How to read first few chareters from table

Author

Topic

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-01-26 : 11:10:13

Hello everybody,

I have a data like this:
Ab001skdj
Ab001kslddf
Ab002kdlskd
Ab005ksls
Ab002keijfd
Ab001kkdkd

How do i read first 5 charecters on each records?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-26 : 11:31:49

You can use the LEFT or SUBSTRING functions.

SELECT LEFT(Column1, 5)
FROM Table1

SELECT SUBSTRING(Column1, 1, 5)
FROM Table1

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-01-26 : 11:44:54

thanks tkizer.

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-01-26 : 12:22:42

I got error when i tried this way:

UPDATE A
SET GID= B.ID
FROM table1 A,
(
SELECT min(ID) ID,SUBSTRING(Column1,1,5) from table1
GROUP By SUBSTRING(Column1,1,5)
Having Count(ID)>=1) B
WHERE SUBSTRING(A.Column1,1,5) = SUBSTRING(B.Column1,1,5)
Go

The error is:

Msg 8155, Level 16, State 2, Line 1
No column was specified for column 2 of 'B'.
Msg 207, Level 16, State 1, Line 8
Invalid column name Column1.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-26 : 12:46:38

You didn't alias min(ID) properly.

... AS SomeAliasName or SomeAliasName = ...

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-01-26 : 12:52:05

When i did not use substring, it was working.

UPDATE A
SET GID= B.ID
FROM table1 A,
(
SELECT min(ID) ID,Column1 from table1
GROUP By Column1
Having Count(ID)>=1) B
WHERE A.Column1 = B.Column1
Go

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-26 : 13:10:20

So what happens when you alias both of them properly?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-01-26 : 13:14:27

I got it. This is work.

UPDATE A
SET GID= B.ID
FROM table1 A,
(
SELECT min(ID) ID,SUBSTRING(Column1,1,5) as Aa from table1
GROUP By SUBSTRING(Column1,1,5)
Having Count(ID)>=1) B
WHERE SUBSTRING(A.Column1,1,5) = b.Aa

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-26 : 13:15:14

Cool, let us know if you need any other help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-01-26 : 14:28:41

Thanks tkizer

I have a simple query problems.
I have 5 fields on a table. The first one is Id, 2nd is text value, it has multiple same value.
but 3rd and 4th fields has different values.
So, what i need, update 5th fields with field 3rd field's value, if exist, if not exist update from 4th field. If both 2nd,3rd and 4th fields are null, 5th fields also null.
We have to group by 2nd field.

The 5th field will be update with first record of 3rd field

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-26 : 14:29:44

I'd suggest starting a new thread on that and also providing good sample data to illustrate your issue.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-01-26 : 14:43:53

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources