Solved

SQL Query Help

Posted on 2012-04-02
7
252 Views
Last Modified: 2012-04-03
Team -

I have a table with a field "Information" in which i would like to get only the first character of all the words.

Example:
"Information" fields contains 5 rows

1. What is my name?
2. Ohio is a nice place.
3. Oh My God !!!
4. Job Well done..,
5. Hearty Welcome to our chairman.

I'd like to run a query which will pull only the first character of each word in each row.

i.e.,
1.WIMN
2.OIANP
3.OMG
4.JWD
5.HWTOC

Requesting your help please
0
Comment
Question by:davidi1
7 Comments
 
LVL 22

Expert Comment

by:Om Prakash
ID: 37794910
Try:
SELECT left(field1,1), left(field2,1) from your_table_name
0
 
LVL 10

Expert Comment

by:Bawer
ID: 37794967
select SUBSTR(first_name,1,1)name from employee
0
 
LVL 15

Expert Comment

by:gplana
ID: 37795031
I think there isn't any function on SQL-Server for getting first character of every word. I think you need to create a user defined function for achieving this and then execute this query:

SELECT FirstCharOfEveryWord(Information) as first_characters
FROM yourTable;

Where FirstCharOfEveryWord is the function you should create. I suppose the idea is to take the first character (by SUBSTR function) and then use a loop to treat every character (gotten also by SUBSTR) and everytime you find a space take next character. But probably you should also treat things like if there are more than one space together, what to do with punctuation signs (point, comma, ...) and things like this, so this function could not be easy.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 18

Accepted Solution

by:
deighton earned 500 total points
ID: 37795083
do this using a function

-- ================================================
-- Template generated from Template Explorer using:
-- Create Scalar Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
CREATE  FUNCTION fn_SentenceChars
(
	-- Add the parameters for the function here
	@SENTENCE varchar(200)
)
RETURNS varchar(200)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @ANS  varchar(200)




DECLARE @I int;
SET @I = 1
WHILE @I < LEN(@SENTENCE)
BEGIN
	IF @I = 1
	BEGIN
	   SET @ANS = LEFT(@SENTENCE, 1);
	END
	ELSE
	BEGIN
		IF SUBSTRING(@SENTENCE, @I - 1, 1) =' ' AND SUBSTRING(@SENTENCE, @I, 1) <> ' '
				SET @ANS = @ANS + SUBSTRING(@SENTENCE, @I, 1); 	 
	END		
	SET @I = @I + 1;
END

-- Return the result of the function
RETURN @ANS



END
GO

Open in new window



then use it on your fields, here is a test I did

select dbo.fn_SentenceChars('the cat sat on the mat')

Open in new window

0
 
LVL 19

Expert Comment

by:Rimvis
ID: 37795277
Hi davidi1,

try this:

SELECT *, 
	(SELECT UPPER(SUBSTRING(Information, n, 1)) 
		FROM YourTable CROSS JOIN (SELECT row_number() over (order by object_id) AS n FROM sys.columns) AS num 
		WHERE ID = y.ID AND SUBSTRING(' ' + Information, n, 1) = ' ' AND n<LEN(Information) FOR XML PATH('')) 
FROM YourTable AS y

Open in new window

0
 
LVL 6

Expert Comment

by:SJCFL-Admin
ID: 37796690
You are obviously doing this as part of another process.   I could not help wondering if you were implementing your own searching mechanism.  In which case, I would highly recommend you looked into implementing a full text index.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 37798509
>> first character of all the words. <<

You have to define what you mean by "word".

Only spaces??

Me personally, I would think something along these lines, *conceptually* (not necessarily directly coded this way):

Find the first/next alpha/numeric char, to start a "word"
All immediately following: alpha,numeric, *and underscores* are part of that "word" (./|~ etc are not)
    First char that is NOT one of those ends the "word".
--Loop/repeat until all words are processed.


Is that right?  Or do you want include other chars within a "word"?
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now